sqlglot.generator

   1from __future__ import annotations
   2
   3import logging
   4import re
   5import typing as t
   6from collections import defaultdict
   7from functools import reduce, wraps
   8
   9from sqlglot import exp
  10from sqlglot.errors import ErrorLevel, UnsupportedError, concat_messages
  11from sqlglot.helper import apply_index_offset, csv, name_sequence, seq_get
  12from sqlglot.jsonpath import ALL_JSON_PATH_PARTS, JSON_PATH_PART_TRANSFORMS
  13from sqlglot.time import format_time
  14from sqlglot.tokens import TokenType
  15
  16if t.TYPE_CHECKING:
  17    from sqlglot._typing import E
  18    from sqlglot.dialects.dialect import DialectType
  19
  20    G = t.TypeVar("G", bound="Generator")
  21    GeneratorMethod = t.Callable[[G, E], str]
  22
  23logger = logging.getLogger("sqlglot")
  24
  25ESCAPED_UNICODE_RE = re.compile(r"\\(\d+)")
  26UNSUPPORTED_TEMPLATE = "Argument '{}' is not supported for expression '{}' when targeting {}."
  27
  28
  29def unsupported_args(
  30    *args: t.Union[str, t.Tuple[str, str]],
  31) -> t.Callable[[GeneratorMethod], GeneratorMethod]:
  32    """
  33    Decorator that can be used to mark certain args of an `Expression` subclass as unsupported.
  34    It expects a sequence of argument names or pairs of the form (argument_name, diagnostic_msg).
  35    """
  36    diagnostic_by_arg: t.Dict[str, t.Optional[str]] = {}
  37    for arg in args:
  38        if isinstance(arg, str):
  39            diagnostic_by_arg[arg] = None
  40        else:
  41            diagnostic_by_arg[arg[0]] = arg[1]
  42
  43    def decorator(func: GeneratorMethod) -> GeneratorMethod:
  44        @wraps(func)
  45        def _func(generator: G, expression: E) -> str:
  46            expression_name = expression.__class__.__name__
  47            dialect_name = generator.dialect.__class__.__name__
  48
  49            for arg_name, diagnostic in diagnostic_by_arg.items():
  50                if expression.args.get(arg_name):
  51                    diagnostic = diagnostic or UNSUPPORTED_TEMPLATE.format(
  52                        arg_name, expression_name, dialect_name
  53                    )
  54                    generator.unsupported(diagnostic)
  55
  56            return func(generator, expression)
  57
  58        return _func
  59
  60    return decorator
  61
  62
  63class _Generator(type):
  64    def __new__(cls, clsname, bases, attrs):
  65        klass = super().__new__(cls, clsname, bases, attrs)
  66
  67        # Remove transforms that correspond to unsupported JSONPathPart expressions
  68        for part in ALL_JSON_PATH_PARTS - klass.SUPPORTED_JSON_PATH_PARTS:
  69            klass.TRANSFORMS.pop(part, None)
  70
  71        return klass
  72
  73
  74class Generator(metaclass=_Generator):
  75    """
  76    Generator converts a given syntax tree to the corresponding SQL string.
  77
  78    Args:
  79        pretty: Whether to format the produced SQL string.
  80            Default: False.
  81        identify: Determines when an identifier should be quoted. Possible values are:
  82            False (default): Never quote, except in cases where it's mandatory by the dialect.
  83            True or 'always': Always quote.
  84            'safe': Only quote identifiers that are case insensitive.
  85        normalize: Whether to normalize identifiers to lowercase.
  86            Default: False.
  87        pad: The pad size in a formatted string. For example, this affects the indentation of
  88            a projection in a query, relative to its nesting level.
  89            Default: 2.
  90        indent: The indentation size in a formatted string. For example, this affects the
  91            indentation of subqueries and filters under a `WHERE` clause.
  92            Default: 2.
  93        normalize_functions: How to normalize function names. Possible values are:
  94            "upper" or True (default): Convert names to uppercase.
  95            "lower": Convert names to lowercase.
  96            False: Disables function name normalization.
  97        unsupported_level: Determines the generator's behavior when it encounters unsupported expressions.
  98            Default ErrorLevel.WARN.
  99        max_unsupported: Maximum number of unsupported messages to include in a raised UnsupportedError.
 100            This is only relevant if unsupported_level is ErrorLevel.RAISE.
 101            Default: 3
 102        leading_comma: Whether the comma is leading or trailing in select expressions.
 103            This is only relevant when generating in pretty mode.
 104            Default: False
 105        max_text_width: The max number of characters in a segment before creating new lines in pretty mode.
 106            The default is on the smaller end because the length only represents a segment and not the true
 107            line length.
 108            Default: 80
 109        comments: Whether to preserve comments in the output SQL code.
 110            Default: True
 111    """
 112
 113    TRANSFORMS: t.Dict[t.Type[exp.Expression], t.Callable[..., str]] = {
 114        **JSON_PATH_PART_TRANSFORMS,
 115        exp.AllowedValuesProperty: lambda self,
 116        e: f"ALLOWED_VALUES {self.expressions(e, flat=True)}",
 117        exp.AnalyzeColumns: lambda self, e: self.sql(e, "this"),
 118        exp.AnalyzeWith: lambda self, e: self.expressions(e, prefix="WITH ", sep=" "),
 119        exp.ArrayContainsAll: lambda self, e: self.binary(e, "@>"),
 120        exp.ArrayOverlaps: lambda self, e: self.binary(e, "&&"),
 121        exp.AutoRefreshProperty: lambda self, e: f"AUTO REFRESH {self.sql(e, 'this')}",
 122        exp.BackupProperty: lambda self, e: f"BACKUP {self.sql(e, 'this')}",
 123        exp.CaseSpecificColumnConstraint: lambda _,
 124        e: f"{'NOT ' if e.args.get('not_') else ''}CASESPECIFIC",
 125        exp.Ceil: lambda self, e: self.ceil_floor(e),
 126        exp.CharacterSetColumnConstraint: lambda self, e: f"CHARACTER SET {self.sql(e, 'this')}",
 127        exp.CharacterSetProperty: lambda self,
 128        e: f"{'DEFAULT ' if e.args.get('default') else ''}CHARACTER SET={self.sql(e, 'this')}",
 129        exp.ClusteredColumnConstraint: lambda self,
 130        e: f"CLUSTERED ({self.expressions(e, 'this', indent=False)})",
 131        exp.CollateColumnConstraint: lambda self, e: f"COLLATE {self.sql(e, 'this')}",
 132        exp.CommentColumnConstraint: lambda self, e: f"COMMENT {self.sql(e, 'this')}",
 133        exp.ConnectByRoot: lambda self, e: f"CONNECT_BY_ROOT {self.sql(e, 'this')}",
 134        exp.ConvertToCharset: lambda self, e: self.func(
 135            "CONVERT", e.this, e.args["dest"], e.args.get("source")
 136        ),
 137        exp.CopyGrantsProperty: lambda *_: "COPY GRANTS",
 138        exp.CredentialsProperty: lambda self,
 139        e: f"CREDENTIALS=({self.expressions(e, 'expressions', sep=' ')})",
 140        exp.DateFormatColumnConstraint: lambda self, e: f"FORMAT {self.sql(e, 'this')}",
 141        exp.DefaultColumnConstraint: lambda self, e: f"DEFAULT {self.sql(e, 'this')}",
 142        exp.DynamicProperty: lambda *_: "DYNAMIC",
 143        exp.EmptyProperty: lambda *_: "EMPTY",
 144        exp.EncodeColumnConstraint: lambda self, e: f"ENCODE {self.sql(e, 'this')}",
 145        exp.EnviromentProperty: lambda self, e: f"ENVIRONMENT ({self.expressions(e, flat=True)})",
 146        exp.EphemeralColumnConstraint: lambda self,
 147        e: f"EPHEMERAL{(' ' + self.sql(e, 'this')) if e.this else ''}",
 148        exp.ExcludeColumnConstraint: lambda self, e: f"EXCLUDE {self.sql(e, 'this').lstrip()}",
 149        exp.ExecuteAsProperty: lambda self, e: self.naked_property(e),
 150        exp.Except: lambda self, e: self.set_operations(e),
 151        exp.ExternalProperty: lambda *_: "EXTERNAL",
 152        exp.Floor: lambda self, e: self.ceil_floor(e),
 153        exp.Get: lambda self, e: self.get_put_sql(e),
 154        exp.GlobalProperty: lambda *_: "GLOBAL",
 155        exp.HeapProperty: lambda *_: "HEAP",
 156        exp.IcebergProperty: lambda *_: "ICEBERG",
 157        exp.InheritsProperty: lambda self, e: f"INHERITS ({self.expressions(e, flat=True)})",
 158        exp.InlineLengthColumnConstraint: lambda self, e: f"INLINE LENGTH {self.sql(e, 'this')}",
 159        exp.InputModelProperty: lambda self, e: f"INPUT{self.sql(e, 'this')}",
 160        exp.Intersect: lambda self, e: self.set_operations(e),
 161        exp.IntervalSpan: lambda self, e: f"{self.sql(e, 'this')} TO {self.sql(e, 'expression')}",
 162        exp.Int64: lambda self, e: self.sql(exp.cast(e.this, exp.DataType.Type.BIGINT)),
 163        exp.LanguageProperty: lambda self, e: self.naked_property(e),
 164        exp.LocationProperty: lambda self, e: self.naked_property(e),
 165        exp.LogProperty: lambda _, e: f"{'NO ' if e.args.get('no') else ''}LOG",
 166        exp.MaterializedProperty: lambda *_: "MATERIALIZED",
 167        exp.NonClusteredColumnConstraint: lambda self,
 168        e: f"NONCLUSTERED ({self.expressions(e, 'this', indent=False)})",
 169        exp.NoPrimaryIndexProperty: lambda *_: "NO PRIMARY INDEX",
 170        exp.NotForReplicationColumnConstraint: lambda *_: "NOT FOR REPLICATION",
 171        exp.OnCommitProperty: lambda _,
 172        e: f"ON COMMIT {'DELETE' if e.args.get('delete') else 'PRESERVE'} ROWS",
 173        exp.OnProperty: lambda self, e: f"ON {self.sql(e, 'this')}",
 174        exp.OnUpdateColumnConstraint: lambda self, e: f"ON UPDATE {self.sql(e, 'this')}",
 175        exp.Operator: lambda self, e: self.binary(e, ""),  # The operator is produced in `binary`
 176        exp.OutputModelProperty: lambda self, e: f"OUTPUT{self.sql(e, 'this')}",
 177        exp.PathColumnConstraint: lambda self, e: f"PATH {self.sql(e, 'this')}",
 178        exp.PartitionedByBucket: lambda self, e: self.func("BUCKET", e.this, e.expression),
 179        exp.PartitionByTruncate: lambda self, e: self.func("TRUNCATE", e.this, e.expression),
 180        exp.PivotAny: lambda self, e: f"ANY{self.sql(e, 'this')}",
 181        exp.PositionalColumn: lambda self, e: f"#{self.sql(e, 'this')}",
 182        exp.ProjectionPolicyColumnConstraint: lambda self,
 183        e: f"PROJECTION POLICY {self.sql(e, 'this')}",
 184        exp.Put: lambda self, e: self.get_put_sql(e),
 185        exp.RemoteWithConnectionModelProperty: lambda self,
 186        e: f"REMOTE WITH CONNECTION {self.sql(e, 'this')}",
 187        exp.ReturnsProperty: lambda self, e: (
 188            "RETURNS NULL ON NULL INPUT" if e.args.get("null") else self.naked_property(e)
 189        ),
 190        exp.SampleProperty: lambda self, e: f"SAMPLE BY {self.sql(e, 'this')}",
 191        exp.SecureProperty: lambda *_: "SECURE",
 192        exp.SecurityProperty: lambda self, e: f"SECURITY {self.sql(e, 'this')}",
 193        exp.SetConfigProperty: lambda self, e: self.sql(e, "this"),
 194        exp.SetProperty: lambda _, e: f"{'MULTI' if e.args.get('multi') else ''}SET",
 195        exp.SettingsProperty: lambda self, e: f"SETTINGS{self.seg('')}{(self.expressions(e))}",
 196        exp.SharingProperty: lambda self, e: f"SHARING={self.sql(e, 'this')}",
 197        exp.SqlReadWriteProperty: lambda _, e: e.name,
 198        exp.SqlSecurityProperty: lambda _,
 199        e: f"SQL SECURITY {'DEFINER' if e.args.get('definer') else 'INVOKER'}",
 200        exp.StabilityProperty: lambda _, e: e.name,
 201        exp.Stream: lambda self, e: f"STREAM {self.sql(e, 'this')}",
 202        exp.StreamingTableProperty: lambda *_: "STREAMING",
 203        exp.StrictProperty: lambda *_: "STRICT",
 204        exp.SwapTable: lambda self, e: f"SWAP WITH {self.sql(e, 'this')}",
 205        exp.TableColumn: lambda self, e: self.sql(e.this),
 206        exp.Tags: lambda self, e: f"TAG ({self.expressions(e, flat=True)})",
 207        exp.TemporaryProperty: lambda *_: "TEMPORARY",
 208        exp.TitleColumnConstraint: lambda self, e: f"TITLE {self.sql(e, 'this')}",
 209        exp.ToMap: lambda self, e: f"MAP {self.sql(e, 'this')}",
 210        exp.ToTableProperty: lambda self, e: f"TO {self.sql(e.this)}",
 211        exp.TransformModelProperty: lambda self, e: self.func("TRANSFORM", *e.expressions),
 212        exp.TransientProperty: lambda *_: "TRANSIENT",
 213        exp.Union: lambda self, e: self.set_operations(e),
 214        exp.UnloggedProperty: lambda *_: "UNLOGGED",
 215        exp.UsingTemplateProperty: lambda self, e: f"USING TEMPLATE {self.sql(e, 'this')}",
 216        exp.UsingData: lambda self, e: f"USING DATA {self.sql(e, 'this')}",
 217        exp.Uuid: lambda *_: "UUID()",
 218        exp.UppercaseColumnConstraint: lambda *_: "UPPERCASE",
 219        exp.VarMap: lambda self, e: self.func("MAP", e.args["keys"], e.args["values"]),
 220        exp.ViewAttributeProperty: lambda self, e: f"WITH {self.sql(e, 'this')}",
 221        exp.VolatileProperty: lambda *_: "VOLATILE",
 222        exp.WithJournalTableProperty: lambda self, e: f"WITH JOURNAL TABLE={self.sql(e, 'this')}",
 223        exp.WithProcedureOptions: lambda self, e: f"WITH {self.expressions(e, flat=True)}",
 224        exp.WithSchemaBindingProperty: lambda self, e: f"WITH SCHEMA {self.sql(e, 'this')}",
 225        exp.WithOperator: lambda self, e: f"{self.sql(e, 'this')} WITH {self.sql(e, 'op')}",
 226        exp.ForceProperty: lambda *_: "FORCE",
 227    }
 228
 229    # Whether null ordering is supported in order by
 230    # True: Full Support, None: No support, False: No support for certain cases
 231    # such as window specifications, aggregate functions etc
 232    NULL_ORDERING_SUPPORTED: t.Optional[bool] = True
 233
 234    # Whether ignore nulls is inside the agg or outside.
 235    # FIRST(x IGNORE NULLS) OVER vs FIRST (x) IGNORE NULLS OVER
 236    IGNORE_NULLS_IN_FUNC = False
 237
 238    # Whether locking reads (i.e. SELECT ... FOR UPDATE/SHARE) are supported
 239    LOCKING_READS_SUPPORTED = False
 240
 241    # Whether the EXCEPT and INTERSECT operations can return duplicates
 242    EXCEPT_INTERSECT_SUPPORT_ALL_CLAUSE = True
 243
 244    # Wrap derived values in parens, usually standard but spark doesn't support it
 245    WRAP_DERIVED_VALUES = True
 246
 247    # Whether create function uses an AS before the RETURN
 248    CREATE_FUNCTION_RETURN_AS = True
 249
 250    # Whether MERGE ... WHEN MATCHED BY SOURCE is allowed
 251    MATCHED_BY_SOURCE = True
 252
 253    # Whether the INTERVAL expression works only with values like '1 day'
 254    SINGLE_STRING_INTERVAL = False
 255
 256    # Whether the plural form of date parts like day (i.e. "days") is supported in INTERVALs
 257    INTERVAL_ALLOWS_PLURAL_FORM = True
 258
 259    # Whether limit and fetch are supported (possible values: "ALL", "LIMIT", "FETCH")
 260    LIMIT_FETCH = "ALL"
 261
 262    # Whether limit and fetch allows expresions or just limits
 263    LIMIT_ONLY_LITERALS = False
 264
 265    # Whether a table is allowed to be renamed with a db
 266    RENAME_TABLE_WITH_DB = True
 267
 268    # The separator for grouping sets and rollups
 269    GROUPINGS_SEP = ","
 270
 271    # The string used for creating an index on a table
 272    INDEX_ON = "ON"
 273
 274    # Whether join hints should be generated
 275    JOIN_HINTS = True
 276
 277    # Whether table hints should be generated
 278    TABLE_HINTS = True
 279
 280    # Whether query hints should be generated
 281    QUERY_HINTS = True
 282
 283    # What kind of separator to use for query hints
 284    QUERY_HINT_SEP = ", "
 285
 286    # Whether comparing against booleans (e.g. x IS TRUE) is supported
 287    IS_BOOL_ALLOWED = True
 288
 289    # Whether to include the "SET" keyword in the "INSERT ... ON DUPLICATE KEY UPDATE" statement
 290    DUPLICATE_KEY_UPDATE_WITH_SET = True
 291
 292    # Whether to generate the limit as TOP <value> instead of LIMIT <value>
 293    LIMIT_IS_TOP = False
 294
 295    # Whether to generate INSERT INTO ... RETURNING or INSERT INTO RETURNING ...
 296    RETURNING_END = True
 297
 298    # Whether to generate an unquoted value for EXTRACT's date part argument
 299    EXTRACT_ALLOWS_QUOTES = True
 300
 301    # Whether TIMETZ / TIMESTAMPTZ will be generated using the "WITH TIME ZONE" syntax
 302    TZ_TO_WITH_TIME_ZONE = False
 303
 304    # Whether the NVL2 function is supported
 305    NVL2_SUPPORTED = True
 306
 307    # https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax
 308    SELECT_KINDS: t.Tuple[str, ...] = ("STRUCT", "VALUE")
 309
 310    # Whether VALUES statements can be used as derived tables.
 311    # MySQL 5 and Redshift do not allow this, so when False, it will convert
 312    # SELECT * VALUES into SELECT UNION
 313    VALUES_AS_TABLE = True
 314
 315    # Whether the word COLUMN is included when adding a column with ALTER TABLE
 316    ALTER_TABLE_INCLUDE_COLUMN_KEYWORD = True
 317
 318    # UNNEST WITH ORDINALITY (presto) instead of UNNEST WITH OFFSET (bigquery)
 319    UNNEST_WITH_ORDINALITY = True
 320
 321    # Whether FILTER (WHERE cond) can be used for conditional aggregation
 322    AGGREGATE_FILTER_SUPPORTED = True
 323
 324    # Whether JOIN sides (LEFT, RIGHT) are supported in conjunction with SEMI/ANTI join kinds
 325    SEMI_ANTI_JOIN_WITH_SIDE = True
 326
 327    # Whether to include the type of a computed column in the CREATE DDL
 328    COMPUTED_COLUMN_WITH_TYPE = True
 329
 330    # Whether CREATE TABLE .. COPY .. is supported. False means we'll generate CLONE instead of COPY
 331    SUPPORTS_TABLE_COPY = True
 332
 333    # Whether parentheses are required around the table sample's expression
 334    TABLESAMPLE_REQUIRES_PARENS = True
 335
 336    # Whether a table sample clause's size needs to be followed by the ROWS keyword
 337    TABLESAMPLE_SIZE_IS_ROWS = True
 338
 339    # The keyword(s) to use when generating a sample clause
 340    TABLESAMPLE_KEYWORDS = "TABLESAMPLE"
 341
 342    # Whether the TABLESAMPLE clause supports a method name, like BERNOULLI
 343    TABLESAMPLE_WITH_METHOD = True
 344
 345    # The keyword to use when specifying the seed of a sample clause
 346    TABLESAMPLE_SEED_KEYWORD = "SEED"
 347
 348    # Whether COLLATE is a function instead of a binary operator
 349    COLLATE_IS_FUNC = False
 350
 351    # Whether data types support additional specifiers like e.g. CHAR or BYTE (oracle)
 352    DATA_TYPE_SPECIFIERS_ALLOWED = False
 353
 354    # Whether conditions require booleans WHERE x = 0 vs WHERE x
 355    ENSURE_BOOLS = False
 356
 357    # Whether the "RECURSIVE" keyword is required when defining recursive CTEs
 358    CTE_RECURSIVE_KEYWORD_REQUIRED = True
 359
 360    # Whether CONCAT requires >1 arguments
 361    SUPPORTS_SINGLE_ARG_CONCAT = True
 362
 363    # Whether LAST_DAY function supports a date part argument
 364    LAST_DAY_SUPPORTS_DATE_PART = True
 365
 366    # Whether named columns are allowed in table aliases
 367    SUPPORTS_TABLE_ALIAS_COLUMNS = True
 368
 369    # Whether UNPIVOT aliases are Identifiers (False means they're Literals)
 370    UNPIVOT_ALIASES_ARE_IDENTIFIERS = True
 371
 372    # What delimiter to use for separating JSON key/value pairs
 373    JSON_KEY_VALUE_PAIR_SEP = ":"
 374
 375    # INSERT OVERWRITE TABLE x override
 376    INSERT_OVERWRITE = " OVERWRITE TABLE"
 377
 378    # Whether the SELECT .. INTO syntax is used instead of CTAS
 379    SUPPORTS_SELECT_INTO = False
 380
 381    # Whether UNLOGGED tables can be created
 382    SUPPORTS_UNLOGGED_TABLES = False
 383
 384    # Whether the CREATE TABLE LIKE statement is supported
 385    SUPPORTS_CREATE_TABLE_LIKE = True
 386
 387    # Whether the LikeProperty needs to be specified inside of the schema clause
 388    LIKE_PROPERTY_INSIDE_SCHEMA = False
 389
 390    # Whether DISTINCT can be followed by multiple args in an AggFunc. If not, it will be
 391    # transpiled into a series of CASE-WHEN-ELSE, ultimately using a tuple conseisting of the args
 392    MULTI_ARG_DISTINCT = True
 393
 394    # Whether the JSON extraction operators expect a value of type JSON
 395    JSON_TYPE_REQUIRED_FOR_EXTRACTION = False
 396
 397    # Whether bracketed keys like ["foo"] are supported in JSON paths
 398    JSON_PATH_BRACKETED_KEY_SUPPORTED = True
 399
 400    # Whether to escape keys using single quotes in JSON paths
 401    JSON_PATH_SINGLE_QUOTE_ESCAPE = False
 402
 403    # The JSONPathPart expressions supported by this dialect
 404    SUPPORTED_JSON_PATH_PARTS = ALL_JSON_PATH_PARTS.copy()
 405
 406    # Whether any(f(x) for x in array) can be implemented by this dialect
 407    CAN_IMPLEMENT_ARRAY_ANY = False
 408
 409    # Whether the function TO_NUMBER is supported
 410    SUPPORTS_TO_NUMBER = True
 411
 412    # Whether EXCLUDE in window specification is supported
 413    SUPPORTS_WINDOW_EXCLUDE = False
 414
 415    # Whether or not set op modifiers apply to the outer set op or select.
 416    # SELECT * FROM x UNION SELECT * FROM y LIMIT 1
 417    # True means limit 1 happens after the set op, False means it it happens on y.
 418    SET_OP_MODIFIERS = True
 419
 420    # Whether parameters from COPY statement are wrapped in parentheses
 421    COPY_PARAMS_ARE_WRAPPED = True
 422
 423    # Whether values of params are set with "=" token or empty space
 424    COPY_PARAMS_EQ_REQUIRED = False
 425
 426    # Whether COPY statement has INTO keyword
 427    COPY_HAS_INTO_KEYWORD = True
 428
 429    # Whether the conditional TRY(expression) function is supported
 430    TRY_SUPPORTED = True
 431
 432    # Whether the UESCAPE syntax in unicode strings is supported
 433    SUPPORTS_UESCAPE = True
 434
 435    # The keyword to use when generating a star projection with excluded columns
 436    STAR_EXCEPT = "EXCEPT"
 437
 438    # The HEX function name
 439    HEX_FUNC = "HEX"
 440
 441    # The keywords to use when prefixing & separating WITH based properties
 442    WITH_PROPERTIES_PREFIX = "WITH"
 443
 444    # Whether to quote the generated expression of exp.JsonPath
 445    QUOTE_JSON_PATH = True
 446
 447    # Whether the text pattern/fill (3rd) parameter of RPAD()/LPAD() is optional (defaults to space)
 448    PAD_FILL_PATTERN_IS_REQUIRED = False
 449
 450    # Whether a projection can explode into multiple rows, e.g. by unnesting an array.
 451    SUPPORTS_EXPLODING_PROJECTIONS = True
 452
 453    # Whether ARRAY_CONCAT can be generated with varlen args or if it should be reduced to 2-arg version
 454    ARRAY_CONCAT_IS_VAR_LEN = True
 455
 456    # Whether CONVERT_TIMEZONE() is supported; if not, it will be generated as exp.AtTimeZone
 457    SUPPORTS_CONVERT_TIMEZONE = False
 458
 459    # Whether MEDIAN(expr) is supported; if not, it will be generated as PERCENTILE_CONT(expr, 0.5)
 460    SUPPORTS_MEDIAN = True
 461
 462    # Whether UNIX_SECONDS(timestamp) is supported
 463    SUPPORTS_UNIX_SECONDS = False
 464
 465    # Whether to wrap <props> in `AlterSet`, e.g., ALTER ... SET (<props>)
 466    ALTER_SET_WRAPPED = False
 467
 468    # Whether to normalize the date parts in EXTRACT(<date_part> FROM <expr>) into a common representation
 469    # For instance, to extract the day of week in ISO semantics, one can use ISODOW, DAYOFWEEKISO etc depending on the dialect.
 470    # TODO: The normalization should be done by default once we've tested it across all dialects.
 471    NORMALIZE_EXTRACT_DATE_PARTS = False
 472
 473    # The name to generate for the JSONPath expression. If `None`, only `this` will be generated
 474    PARSE_JSON_NAME: t.Optional[str] = "PARSE_JSON"
 475
 476    # The function name of the exp.ArraySize expression
 477    ARRAY_SIZE_NAME: str = "ARRAY_LENGTH"
 478
 479    # The syntax to use when altering the type of a column
 480    ALTER_SET_TYPE = "SET DATA TYPE"
 481
 482    # Whether exp.ArraySize should generate the dimension arg too (valid for Postgres & DuckDB)
 483    # None -> Doesn't support it at all
 484    # False (DuckDB) -> Has backwards-compatible support, but preferably generated without
 485    # True (Postgres) -> Explicitly requires it
 486    ARRAY_SIZE_DIM_REQUIRED: t.Optional[bool] = None
 487
 488    # Whether a multi-argument DECODE(...) function is supported. If not, a CASE expression is generated
 489    SUPPORTS_DECODE_CASE = True
 490
 491    TYPE_MAPPING = {
 492        exp.DataType.Type.DATETIME2: "TIMESTAMP",
 493        exp.DataType.Type.NCHAR: "CHAR",
 494        exp.DataType.Type.NVARCHAR: "VARCHAR",
 495        exp.DataType.Type.MEDIUMTEXT: "TEXT",
 496        exp.DataType.Type.LONGTEXT: "TEXT",
 497        exp.DataType.Type.TINYTEXT: "TEXT",
 498        exp.DataType.Type.BLOB: "VARBINARY",
 499        exp.DataType.Type.MEDIUMBLOB: "BLOB",
 500        exp.DataType.Type.LONGBLOB: "BLOB",
 501        exp.DataType.Type.TINYBLOB: "BLOB",
 502        exp.DataType.Type.INET: "INET",
 503        exp.DataType.Type.ROWVERSION: "VARBINARY",
 504        exp.DataType.Type.SMALLDATETIME: "TIMESTAMP",
 505    }
 506
 507    TIME_PART_SINGULARS = {
 508        "MICROSECONDS": "MICROSECOND",
 509        "SECONDS": "SECOND",
 510        "MINUTES": "MINUTE",
 511        "HOURS": "HOUR",
 512        "DAYS": "DAY",
 513        "WEEKS": "WEEK",
 514        "MONTHS": "MONTH",
 515        "QUARTERS": "QUARTER",
 516        "YEARS": "YEAR",
 517    }
 518
 519    AFTER_HAVING_MODIFIER_TRANSFORMS = {
 520        "cluster": lambda self, e: self.sql(e, "cluster"),
 521        "distribute": lambda self, e: self.sql(e, "distribute"),
 522        "sort": lambda self, e: self.sql(e, "sort"),
 523        "windows": lambda self, e: (
 524            self.seg("WINDOW ") + self.expressions(e, key="windows", flat=True)
 525            if e.args.get("windows")
 526            else ""
 527        ),
 528        "qualify": lambda self, e: self.sql(e, "qualify"),
 529    }
 530
 531    TOKEN_MAPPING: t.Dict[TokenType, str] = {}
 532
 533    STRUCT_DELIMITER = ("<", ">")
 534
 535    PARAMETER_TOKEN = "@"
 536    NAMED_PLACEHOLDER_TOKEN = ":"
 537
 538    EXPRESSION_PRECEDES_PROPERTIES_CREATABLES: t.Set[str] = set()
 539
 540    PROPERTIES_LOCATION = {
 541        exp.AllowedValuesProperty: exp.Properties.Location.POST_SCHEMA,
 542        exp.AlgorithmProperty: exp.Properties.Location.POST_CREATE,
 543        exp.AutoIncrementProperty: exp.Properties.Location.POST_SCHEMA,
 544        exp.AutoRefreshProperty: exp.Properties.Location.POST_SCHEMA,
 545        exp.BackupProperty: exp.Properties.Location.POST_SCHEMA,
 546        exp.BlockCompressionProperty: exp.Properties.Location.POST_NAME,
 547        exp.CharacterSetProperty: exp.Properties.Location.POST_SCHEMA,
 548        exp.ChecksumProperty: exp.Properties.Location.POST_NAME,
 549        exp.CollateProperty: exp.Properties.Location.POST_SCHEMA,
 550        exp.CopyGrantsProperty: exp.Properties.Location.POST_SCHEMA,
 551        exp.Cluster: exp.Properties.Location.POST_SCHEMA,
 552        exp.ClusteredByProperty: exp.Properties.Location.POST_SCHEMA,
 553        exp.DistributedByProperty: exp.Properties.Location.POST_SCHEMA,
 554        exp.DuplicateKeyProperty: exp.Properties.Location.POST_SCHEMA,
 555        exp.DataBlocksizeProperty: exp.Properties.Location.POST_NAME,
 556        exp.DataDeletionProperty: exp.Properties.Location.POST_SCHEMA,
 557        exp.DefinerProperty: exp.Properties.Location.POST_CREATE,
 558        exp.DictRange: exp.Properties.Location.POST_SCHEMA,
 559        exp.DictProperty: exp.Properties.Location.POST_SCHEMA,
 560        exp.DynamicProperty: exp.Properties.Location.POST_CREATE,
 561        exp.DistKeyProperty: exp.Properties.Location.POST_SCHEMA,
 562        exp.DistStyleProperty: exp.Properties.Location.POST_SCHEMA,
 563        exp.EmptyProperty: exp.Properties.Location.POST_SCHEMA,
 564        exp.EncodeProperty: exp.Properties.Location.POST_EXPRESSION,
 565        exp.EngineProperty: exp.Properties.Location.POST_SCHEMA,
 566        exp.EnviromentProperty: exp.Properties.Location.POST_SCHEMA,
 567        exp.ExecuteAsProperty: exp.Properties.Location.POST_SCHEMA,
 568        exp.ExternalProperty: exp.Properties.Location.POST_CREATE,
 569        exp.FallbackProperty: exp.Properties.Location.POST_NAME,
 570        exp.FileFormatProperty: exp.Properties.Location.POST_WITH,
 571        exp.FreespaceProperty: exp.Properties.Location.POST_NAME,
 572        exp.GlobalProperty: exp.Properties.Location.POST_CREATE,
 573        exp.HeapProperty: exp.Properties.Location.POST_WITH,
 574        exp.InheritsProperty: exp.Properties.Location.POST_SCHEMA,
 575        exp.IcebergProperty: exp.Properties.Location.POST_CREATE,
 576        exp.IncludeProperty: exp.Properties.Location.POST_SCHEMA,
 577        exp.InputModelProperty: exp.Properties.Location.POST_SCHEMA,
 578        exp.IsolatedLoadingProperty: exp.Properties.Location.POST_NAME,
 579        exp.JournalProperty: exp.Properties.Location.POST_NAME,
 580        exp.LanguageProperty: exp.Properties.Location.POST_SCHEMA,
 581        exp.LikeProperty: exp.Properties.Location.POST_SCHEMA,
 582        exp.LocationProperty: exp.Properties.Location.POST_SCHEMA,
 583        exp.LockProperty: exp.Properties.Location.POST_SCHEMA,
 584        exp.LockingProperty: exp.Properties.Location.POST_ALIAS,
 585        exp.LogProperty: exp.Properties.Location.POST_NAME,
 586        exp.MaterializedProperty: exp.Properties.Location.POST_CREATE,
 587        exp.MergeBlockRatioProperty: exp.Properties.Location.POST_NAME,
 588        exp.NoPrimaryIndexProperty: exp.Properties.Location.POST_EXPRESSION,
 589        exp.OnProperty: exp.Properties.Location.POST_SCHEMA,
 590        exp.OnCommitProperty: exp.Properties.Location.POST_EXPRESSION,
 591        exp.Order: exp.Properties.Location.POST_SCHEMA,
 592        exp.OutputModelProperty: exp.Properties.Location.POST_SCHEMA,
 593        exp.PartitionedByProperty: exp.Properties.Location.POST_WITH,
 594        exp.PartitionedOfProperty: exp.Properties.Location.POST_SCHEMA,
 595        exp.PrimaryKey: exp.Properties.Location.POST_SCHEMA,
 596        exp.Property: exp.Properties.Location.POST_WITH,
 597        exp.RemoteWithConnectionModelProperty: exp.Properties.Location.POST_SCHEMA,
 598        exp.ReturnsProperty: exp.Properties.Location.POST_SCHEMA,
 599        exp.RowFormatProperty: exp.Properties.Location.POST_SCHEMA,
 600        exp.RowFormatDelimitedProperty: exp.Properties.Location.POST_SCHEMA,
 601        exp.RowFormatSerdeProperty: exp.Properties.Location.POST_SCHEMA,
 602        exp.SampleProperty: exp.Properties.Location.POST_SCHEMA,
 603        exp.SchemaCommentProperty: exp.Properties.Location.POST_SCHEMA,
 604        exp.SecureProperty: exp.Properties.Location.POST_CREATE,
 605        exp.SecurityProperty: exp.Properties.Location.POST_SCHEMA,
 606        exp.SerdeProperties: exp.Properties.Location.POST_SCHEMA,
 607        exp.Set: exp.Properties.Location.POST_SCHEMA,
 608        exp.SettingsProperty: exp.Properties.Location.POST_SCHEMA,
 609        exp.SetProperty: exp.Properties.Location.POST_CREATE,
 610        exp.SetConfigProperty: exp.Properties.Location.POST_SCHEMA,
 611        exp.SharingProperty: exp.Properties.Location.POST_EXPRESSION,
 612        exp.SequenceProperties: exp.Properties.Location.POST_EXPRESSION,
 613        exp.SortKeyProperty: exp.Properties.Location.POST_SCHEMA,
 614        exp.SqlReadWriteProperty: exp.Properties.Location.POST_SCHEMA,
 615        exp.SqlSecurityProperty: exp.Properties.Location.POST_CREATE,
 616        exp.StabilityProperty: exp.Properties.Location.POST_SCHEMA,
 617        exp.StorageHandlerProperty: exp.Properties.Location.POST_SCHEMA,
 618        exp.StreamingTableProperty: exp.Properties.Location.POST_CREATE,
 619        exp.StrictProperty: exp.Properties.Location.POST_SCHEMA,
 620        exp.Tags: exp.Properties.Location.POST_WITH,
 621        exp.TemporaryProperty: exp.Properties.Location.POST_CREATE,
 622        exp.ToTableProperty: exp.Properties.Location.POST_SCHEMA,
 623        exp.TransientProperty: exp.Properties.Location.POST_CREATE,
 624        exp.TransformModelProperty: exp.Properties.Location.POST_SCHEMA,
 625        exp.MergeTreeTTL: exp.Properties.Location.POST_SCHEMA,
 626        exp.UnloggedProperty: exp.Properties.Location.POST_CREATE,
 627        exp.UsingTemplateProperty: exp.Properties.Location.POST_SCHEMA,
 628        exp.ViewAttributeProperty: exp.Properties.Location.POST_SCHEMA,
 629        exp.VolatileProperty: exp.Properties.Location.POST_CREATE,
 630        exp.WithDataProperty: exp.Properties.Location.POST_EXPRESSION,
 631        exp.WithJournalTableProperty: exp.Properties.Location.POST_NAME,
 632        exp.WithProcedureOptions: exp.Properties.Location.POST_SCHEMA,
 633        exp.WithSchemaBindingProperty: exp.Properties.Location.POST_SCHEMA,
 634        exp.WithSystemVersioningProperty: exp.Properties.Location.POST_SCHEMA,
 635        exp.ForceProperty: exp.Properties.Location.POST_CREATE,
 636    }
 637
 638    # Keywords that can't be used as unquoted identifier names
 639    RESERVED_KEYWORDS: t.Set[str] = set()
 640
 641    # Expressions whose comments are separated from them for better formatting
 642    WITH_SEPARATED_COMMENTS: t.Tuple[t.Type[exp.Expression], ...] = (
 643        exp.Command,
 644        exp.Create,
 645        exp.Describe,
 646        exp.Delete,
 647        exp.Drop,
 648        exp.From,
 649        exp.Insert,
 650        exp.Join,
 651        exp.MultitableInserts,
 652        exp.Order,
 653        exp.Group,
 654        exp.Having,
 655        exp.Select,
 656        exp.SetOperation,
 657        exp.Update,
 658        exp.Where,
 659        exp.With,
 660    )
 661
 662    # Expressions that should not have their comments generated in maybe_comment
 663    EXCLUDE_COMMENTS: t.Tuple[t.Type[exp.Expression], ...] = (
 664        exp.Binary,
 665        exp.SetOperation,
 666    )
 667
 668    # Expressions that can remain unwrapped when appearing in the context of an INTERVAL
 669    UNWRAPPED_INTERVAL_VALUES: t.Tuple[t.Type[exp.Expression], ...] = (
 670        exp.Column,
 671        exp.Literal,
 672        exp.Neg,
 673        exp.Paren,
 674    )
 675
 676    PARAMETERIZABLE_TEXT_TYPES = {
 677        exp.DataType.Type.NVARCHAR,
 678        exp.DataType.Type.VARCHAR,
 679        exp.DataType.Type.CHAR,
 680        exp.DataType.Type.NCHAR,
 681    }
 682
 683    # Expressions that need to have all CTEs under them bubbled up to them
 684    EXPRESSIONS_WITHOUT_NESTED_CTES: t.Set[t.Type[exp.Expression]] = set()
 685
 686    RESPECT_IGNORE_NULLS_UNSUPPORTED_EXPRESSIONS: t.Tuple[t.Type[exp.Expression], ...] = ()
 687
 688    SENTINEL_LINE_BREAK = "__SQLGLOT__LB__"
 689
 690    __slots__ = (
 691        "pretty",
 692        "identify",
 693        "normalize",
 694        "pad",
 695        "_indent",
 696        "normalize_functions",
 697        "unsupported_level",
 698        "max_unsupported",
 699        "leading_comma",
 700        "max_text_width",
 701        "comments",
 702        "dialect",
 703        "unsupported_messages",
 704        "_escaped_quote_end",
 705        "_escaped_identifier_end",
 706        "_next_name",
 707        "_identifier_start",
 708        "_identifier_end",
 709        "_quote_json_path_key_using_brackets",
 710    )
 711
 712    def __init__(
 713        self,
 714        pretty: t.Optional[bool] = None,
 715        identify: str | bool = False,
 716        normalize: bool = False,
 717        pad: int = 2,
 718        indent: int = 2,
 719        normalize_functions: t.Optional[str | bool] = None,
 720        unsupported_level: ErrorLevel = ErrorLevel.WARN,
 721        max_unsupported: int = 3,
 722        leading_comma: bool = False,
 723        max_text_width: int = 80,
 724        comments: bool = True,
 725        dialect: DialectType = None,
 726    ):
 727        import sqlglot
 728        from sqlglot.dialects import Dialect
 729
 730        self.pretty = pretty if pretty is not None else sqlglot.pretty
 731        self.identify = identify
 732        self.normalize = normalize
 733        self.pad = pad
 734        self._indent = indent
 735        self.unsupported_level = unsupported_level
 736        self.max_unsupported = max_unsupported
 737        self.leading_comma = leading_comma
 738        self.max_text_width = max_text_width
 739        self.comments = comments
 740        self.dialect = Dialect.get_or_raise(dialect)
 741
 742        # This is both a Dialect property and a Generator argument, so we prioritize the latter
 743        self.normalize_functions = (
 744            self.dialect.NORMALIZE_FUNCTIONS if normalize_functions is None else normalize_functions
 745        )
 746
 747        self.unsupported_messages: t.List[str] = []
 748        self._escaped_quote_end: str = (
 749            self.dialect.tokenizer_class.STRING_ESCAPES[0] + self.dialect.QUOTE_END
 750        )
 751        self._escaped_identifier_end = self.dialect.IDENTIFIER_END * 2
 752
 753        self._next_name = name_sequence("_t")
 754
 755        self._identifier_start = self.dialect.IDENTIFIER_START
 756        self._identifier_end = self.dialect.IDENTIFIER_END
 757
 758        self._quote_json_path_key_using_brackets = True
 759
 760    def generate(self, expression: exp.Expression, copy: bool = True) -> str:
 761        """
 762        Generates the SQL string corresponding to the given syntax tree.
 763
 764        Args:
 765            expression: The syntax tree.
 766            copy: Whether to copy the expression. The generator performs mutations so
 767                it is safer to copy.
 768
 769        Returns:
 770            The SQL string corresponding to `expression`.
 771        """
 772        if copy:
 773            expression = expression.copy()
 774
 775        expression = self.preprocess(expression)
 776
 777        self.unsupported_messages = []
 778        sql = self.sql(expression).strip()
 779
 780        if self.pretty:
 781            sql = sql.replace(self.SENTINEL_LINE_BREAK, "\n")
 782
 783        if self.unsupported_level == ErrorLevel.IGNORE:
 784            return sql
 785
 786        if self.unsupported_level == ErrorLevel.WARN:
 787            for msg in self.unsupported_messages:
 788                logger.warning(msg)
 789        elif self.unsupported_level == ErrorLevel.RAISE and self.unsupported_messages:
 790            raise UnsupportedError(concat_messages(self.unsupported_messages, self.max_unsupported))
 791
 792        return sql
 793
 794    def preprocess(self, expression: exp.Expression) -> exp.Expression:
 795        """Apply generic preprocessing transformations to a given expression."""
 796        expression = self._move_ctes_to_top_level(expression)
 797
 798        if self.ENSURE_BOOLS:
 799            from sqlglot.transforms import ensure_bools
 800
 801            expression = ensure_bools(expression)
 802
 803        return expression
 804
 805    def _move_ctes_to_top_level(self, expression: E) -> E:
 806        if (
 807            not expression.parent
 808            and type(expression) in self.EXPRESSIONS_WITHOUT_NESTED_CTES
 809            and any(node.parent is not expression for node in expression.find_all(exp.With))
 810        ):
 811            from sqlglot.transforms import move_ctes_to_top_level
 812
 813            expression = move_ctes_to_top_level(expression)
 814        return expression
 815
 816    def unsupported(self, message: str) -> None:
 817        if self.unsupported_level == ErrorLevel.IMMEDIATE:
 818            raise UnsupportedError(message)
 819        self.unsupported_messages.append(message)
 820
 821    def sep(self, sep: str = " ") -> str:
 822        return f"{sep.strip()}\n" if self.pretty else sep
 823
 824    def seg(self, sql: str, sep: str = " ") -> str:
 825        return f"{self.sep(sep)}{sql}"
 826
 827    def sanitize_comment(self, comment: str) -> str:
 828        comment = " " + comment if comment[0].strip() else comment
 829        comment = comment + " " if comment[-1].strip() else comment
 830
 831        if not self.dialect.tokenizer_class.NESTED_COMMENTS:
 832            # Necessary workaround to avoid syntax errors due to nesting: /* ... */ ... */
 833            comment = comment.replace("*/", "* /")
 834
 835        return comment
 836
 837    def maybe_comment(
 838        self,
 839        sql: str,
 840        expression: t.Optional[exp.Expression] = None,
 841        comments: t.Optional[t.List[str]] = None,
 842        separated: bool = False,
 843    ) -> str:
 844        comments = (
 845            ((expression and expression.comments) if comments is None else comments)  # type: ignore
 846            if self.comments
 847            else None
 848        )
 849
 850        if not comments or isinstance(expression, self.EXCLUDE_COMMENTS):
 851            return sql
 852
 853        comments_sql = " ".join(
 854            f"/*{self.sanitize_comment(comment)}*/" for comment in comments if comment
 855        )
 856
 857        if not comments_sql:
 858            return sql
 859
 860        comments_sql = self._replace_line_breaks(comments_sql)
 861
 862        if separated or isinstance(expression, self.WITH_SEPARATED_COMMENTS):
 863            return (
 864                f"{self.sep()}{comments_sql}{sql}"
 865                if not sql or sql[0].isspace()
 866                else f"{comments_sql}{self.sep()}{sql}"
 867            )
 868
 869        return f"{sql} {comments_sql}"
 870
 871    def wrap(self, expression: exp.Expression | str) -> str:
 872        this_sql = (
 873            self.sql(expression)
 874            if isinstance(expression, exp.UNWRAPPED_QUERIES)
 875            else self.sql(expression, "this")
 876        )
 877        if not this_sql:
 878            return "()"
 879
 880        this_sql = self.indent(this_sql, level=1, pad=0)
 881        return f"({self.sep('')}{this_sql}{self.seg(')', sep='')}"
 882
 883    def no_identify(self, func: t.Callable[..., str], *args, **kwargs) -> str:
 884        original = self.identify
 885        self.identify = False
 886        result = func(*args, **kwargs)
 887        self.identify = original
 888        return result
 889
 890    def normalize_func(self, name: str) -> str:
 891        if self.normalize_functions == "upper" or self.normalize_functions is True:
 892            return name.upper()
 893        if self.normalize_functions == "lower":
 894            return name.lower()
 895        return name
 896
 897    def indent(
 898        self,
 899        sql: str,
 900        level: int = 0,
 901        pad: t.Optional[int] = None,
 902        skip_first: bool = False,
 903        skip_last: bool = False,
 904    ) -> str:
 905        if not self.pretty or not sql:
 906            return sql
 907
 908        pad = self.pad if pad is None else pad
 909        lines = sql.split("\n")
 910
 911        return "\n".join(
 912            (
 913                line
 914                if (skip_first and i == 0) or (skip_last and i == len(lines) - 1)
 915                else f"{' ' * (level * self._indent + pad)}{line}"
 916            )
 917            for i, line in enumerate(lines)
 918        )
 919
 920    def sql(
 921        self,
 922        expression: t.Optional[str | exp.Expression],
 923        key: t.Optional[str] = None,
 924        comment: bool = True,
 925    ) -> str:
 926        if not expression:
 927            return ""
 928
 929        if isinstance(expression, str):
 930            return expression
 931
 932        if key:
 933            value = expression.args.get(key)
 934            if value:
 935                return self.sql(value)
 936            return ""
 937
 938        transform = self.TRANSFORMS.get(expression.__class__)
 939
 940        if callable(transform):
 941            sql = transform(self, expression)
 942        elif isinstance(expression, exp.Expression):
 943            exp_handler_name = f"{expression.key}_sql"
 944
 945            if hasattr(self, exp_handler_name):
 946                sql = getattr(self, exp_handler_name)(expression)
 947            elif isinstance(expression, exp.Func):
 948                sql = self.function_fallback_sql(expression)
 949            elif isinstance(expression, exp.Property):
 950                sql = self.property_sql(expression)
 951            else:
 952                raise ValueError(f"Unsupported expression type {expression.__class__.__name__}")
 953        else:
 954            raise ValueError(f"Expected an Expression. Received {type(expression)}: {expression}")
 955
 956        return self.maybe_comment(sql, expression) if self.comments and comment else sql
 957
 958    def uncache_sql(self, expression: exp.Uncache) -> str:
 959        table = self.sql(expression, "this")
 960        exists_sql = " IF EXISTS" if expression.args.get("exists") else ""
 961        return f"UNCACHE TABLE{exists_sql} {table}"
 962
 963    def cache_sql(self, expression: exp.Cache) -> str:
 964        lazy = " LAZY" if expression.args.get("lazy") else ""
 965        table = self.sql(expression, "this")
 966        options = expression.args.get("options")
 967        options = f" OPTIONS({self.sql(options[0])} = {self.sql(options[1])})" if options else ""
 968        sql = self.sql(expression, "expression")
 969        sql = f" AS{self.sep()}{sql}" if sql else ""
 970        sql = f"CACHE{lazy} TABLE {table}{options}{sql}"
 971        return self.prepend_ctes(expression, sql)
 972
 973    def characterset_sql(self, expression: exp.CharacterSet) -> str:
 974        if isinstance(expression.parent, exp.Cast):
 975            return f"CHAR CHARACTER SET {self.sql(expression, 'this')}"
 976        default = "DEFAULT " if expression.args.get("default") else ""
 977        return f"{default}CHARACTER SET={self.sql(expression, 'this')}"
 978
 979    def column_parts(self, expression: exp.Column) -> str:
 980        return ".".join(
 981            self.sql(part)
 982            for part in (
 983                expression.args.get("catalog"),
 984                expression.args.get("db"),
 985                expression.args.get("table"),
 986                expression.args.get("this"),
 987            )
 988            if part
 989        )
 990
 991    def column_sql(self, expression: exp.Column) -> str:
 992        join_mark = " (+)" if expression.args.get("join_mark") else ""
 993
 994        if join_mark and not self.dialect.SUPPORTS_COLUMN_JOIN_MARKS:
 995            join_mark = ""
 996            self.unsupported("Outer join syntax using the (+) operator is not supported.")
 997
 998        return f"{self.column_parts(expression)}{join_mark}"
 999
1000    def columnposition_sql(self, expression: exp.ColumnPosition) -> str:
1001        this = self.sql(expression, "this")
1002        this = f" {this}" if this else ""
1003        position = self.sql(expression, "position")
1004        return f"{position}{this}"
1005
1006    def columndef_sql(self, expression: exp.ColumnDef, sep: str = " ") -> str:
1007        column = self.sql(expression, "this")
1008        kind = self.sql(expression, "kind")
1009        constraints = self.expressions(expression, key="constraints", sep=" ", flat=True)
1010        exists = "IF NOT EXISTS " if expression.args.get("exists") else ""
1011        kind = f"{sep}{kind}" if kind else ""
1012        constraints = f" {constraints}" if constraints else ""
1013        position = self.sql(expression, "position")
1014        position = f" {position}" if position else ""
1015
1016        if expression.find(exp.ComputedColumnConstraint) and not self.COMPUTED_COLUMN_WITH_TYPE:
1017            kind = ""
1018
1019        return f"{exists}{column}{kind}{constraints}{position}"
1020
1021    def columnconstraint_sql(self, expression: exp.ColumnConstraint) -> str:
1022        this = self.sql(expression, "this")
1023        kind_sql = self.sql(expression, "kind").strip()
1024        return f"CONSTRAINT {this} {kind_sql}" if this else kind_sql
1025
1026    def computedcolumnconstraint_sql(self, expression: exp.ComputedColumnConstraint) -> str:
1027        this = self.sql(expression, "this")
1028        if expression.args.get("not_null"):
1029            persisted = " PERSISTED NOT NULL"
1030        elif expression.args.get("persisted"):
1031            persisted = " PERSISTED"
1032        else:
1033            persisted = ""
1034
1035        return f"AS {this}{persisted}"
1036
1037    def autoincrementcolumnconstraint_sql(self, _) -> str:
1038        return self.token_sql(TokenType.AUTO_INCREMENT)
1039
1040    def compresscolumnconstraint_sql(self, expression: exp.CompressColumnConstraint) -> str:
1041        if isinstance(expression.this, list):
1042            this = self.wrap(self.expressions(expression, key="this", flat=True))
1043        else:
1044            this = self.sql(expression, "this")
1045
1046        return f"COMPRESS {this}"
1047
1048    def generatedasidentitycolumnconstraint_sql(
1049        self, expression: exp.GeneratedAsIdentityColumnConstraint
1050    ) -> str:
1051        this = ""
1052        if expression.this is not None:
1053            on_null = " ON NULL" if expression.args.get("on_null") else ""
1054            this = " ALWAYS" if expression.this else f" BY DEFAULT{on_null}"
1055
1056        start = expression.args.get("start")
1057        start = f"START WITH {start}" if start else ""
1058        increment = expression.args.get("increment")
1059        increment = f" INCREMENT BY {increment}" if increment else ""
1060        minvalue = expression.args.get("minvalue")
1061        minvalue = f" MINVALUE {minvalue}" if minvalue else ""
1062        maxvalue = expression.args.get("maxvalue")
1063        maxvalue = f" MAXVALUE {maxvalue}" if maxvalue else ""
1064        cycle = expression.args.get("cycle")
1065        cycle_sql = ""
1066
1067        if cycle is not None:
1068            cycle_sql = f"{' NO' if not cycle else ''} CYCLE"
1069            cycle_sql = cycle_sql.strip() if not start and not increment else cycle_sql
1070
1071        sequence_opts = ""
1072        if start or increment or cycle_sql:
1073            sequence_opts = f"{start}{increment}{minvalue}{maxvalue}{cycle_sql}"
1074            sequence_opts = f" ({sequence_opts.strip()})"
1075
1076        expr = self.sql(expression, "expression")
1077        expr = f"({expr})" if expr else "IDENTITY"
1078
1079        return f"GENERATED{this} AS {expr}{sequence_opts}"
1080
1081    def generatedasrowcolumnconstraint_sql(
1082        self, expression: exp.GeneratedAsRowColumnConstraint
1083    ) -> str:
1084        start = "START" if expression.args.get("start") else "END"
1085        hidden = " HIDDEN" if expression.args.get("hidden") else ""
1086        return f"GENERATED ALWAYS AS ROW {start}{hidden}"
1087
1088    def periodforsystemtimeconstraint_sql(
1089        self, expression: exp.PeriodForSystemTimeConstraint
1090    ) -> str:
1091        return f"PERIOD FOR SYSTEM_TIME ({self.sql(expression, 'this')}, {self.sql(expression, 'expression')})"
1092
1093    def notnullcolumnconstraint_sql(self, expression: exp.NotNullColumnConstraint) -> str:
1094        return f"{'' if expression.args.get('allow_null') else 'NOT '}NULL"
1095
1096    def primarykeycolumnconstraint_sql(self, expression: exp.PrimaryKeyColumnConstraint) -> str:
1097        desc = expression.args.get("desc")
1098        if desc is not None:
1099            return f"PRIMARY KEY{' DESC' if desc else ' ASC'}"
1100        options = self.expressions(expression, key="options", flat=True, sep=" ")
1101        options = f" {options}" if options else ""
1102        return f"PRIMARY KEY{options}"
1103
1104    def uniquecolumnconstraint_sql(self, expression: exp.UniqueColumnConstraint) -> str:
1105        this = self.sql(expression, "this")
1106        this = f" {this}" if this else ""
1107        index_type = expression.args.get("index_type")
1108        index_type = f" USING {index_type}" if index_type else ""
1109        on_conflict = self.sql(expression, "on_conflict")
1110        on_conflict = f" {on_conflict}" if on_conflict else ""
1111        nulls_sql = " NULLS NOT DISTINCT" if expression.args.get("nulls") else ""
1112        options = self.expressions(expression, key="options", flat=True, sep=" ")
1113        options = f" {options}" if options else ""
1114        return f"UNIQUE{nulls_sql}{this}{index_type}{on_conflict}{options}"
1115
1116    def createable_sql(self, expression: exp.Create, locations: t.DefaultDict) -> str:
1117        return self.sql(expression, "this")
1118
1119    def create_sql(self, expression: exp.Create) -> str:
1120        kind = self.sql(expression, "kind")
1121        kind = self.dialect.INVERSE_CREATABLE_KIND_MAPPING.get(kind) or kind
1122        properties = expression.args.get("properties")
1123        properties_locs = self.locate_properties(properties) if properties else defaultdict()
1124
1125        this = self.createable_sql(expression, properties_locs)
1126
1127        properties_sql = ""
1128        if properties_locs.get(exp.Properties.Location.POST_SCHEMA) or properties_locs.get(
1129            exp.Properties.Location.POST_WITH
1130        ):
1131            properties_sql = self.sql(
1132                exp.Properties(
1133                    expressions=[
1134                        *properties_locs[exp.Properties.Location.POST_SCHEMA],
1135                        *properties_locs[exp.Properties.Location.POST_WITH],
1136                    ]
1137                )
1138            )
1139
1140            if properties_locs.get(exp.Properties.Location.POST_SCHEMA):
1141                properties_sql = self.sep() + properties_sql
1142            elif not self.pretty:
1143                # Standalone POST_WITH properties need a leading whitespace in non-pretty mode
1144                properties_sql = f" {properties_sql}"
1145
1146        begin = " BEGIN" if expression.args.get("begin") else ""
1147        end = " END" if expression.args.get("end") else ""
1148
1149        expression_sql = self.sql(expression, "expression")
1150        if expression_sql:
1151            expression_sql = f"{begin}{self.sep()}{expression_sql}{end}"
1152
1153            if self.CREATE_FUNCTION_RETURN_AS or not isinstance(expression.expression, exp.Return):
1154                postalias_props_sql = ""
1155                if properties_locs.get(exp.Properties.Location.POST_ALIAS):
1156                    postalias_props_sql = self.properties(
1157                        exp.Properties(
1158                            expressions=properties_locs[exp.Properties.Location.POST_ALIAS]
1159                        ),
1160                        wrapped=False,
1161                    )
1162                postalias_props_sql = f" {postalias_props_sql}" if postalias_props_sql else ""
1163                expression_sql = f" AS{postalias_props_sql}{expression_sql}"
1164
1165        postindex_props_sql = ""
1166        if properties_locs.get(exp.Properties.Location.POST_INDEX):
1167            postindex_props_sql = self.properties(
1168                exp.Properties(expressions=properties_locs[exp.Properties.Location.POST_INDEX]),
1169                wrapped=False,
1170                prefix=" ",
1171            )
1172
1173        indexes = self.expressions(expression, key="indexes", indent=False, sep=" ")
1174        indexes = f" {indexes}" if indexes else ""
1175        index_sql = indexes + postindex_props_sql
1176
1177        replace = " OR REPLACE" if expression.args.get("replace") else ""
1178        refresh = " OR REFRESH" if expression.args.get("refresh") else ""
1179        unique = " UNIQUE" if expression.args.get("unique") else ""
1180
1181        clustered = expression.args.get("clustered")
1182        if clustered is None:
1183            clustered_sql = ""
1184        elif clustered:
1185            clustered_sql = " CLUSTERED COLUMNSTORE"
1186        else:
1187            clustered_sql = " NONCLUSTERED COLUMNSTORE"
1188
1189        postcreate_props_sql = ""
1190        if properties_locs.get(exp.Properties.Location.POST_CREATE):
1191            postcreate_props_sql = self.properties(
1192                exp.Properties(expressions=properties_locs[exp.Properties.Location.POST_CREATE]),
1193                sep=" ",
1194                prefix=" ",
1195                wrapped=False,
1196            )
1197
1198        modifiers = "".join((clustered_sql, replace, refresh, unique, postcreate_props_sql))
1199
1200        postexpression_props_sql = ""
1201        if properties_locs.get(exp.Properties.Location.POST_EXPRESSION):
1202            postexpression_props_sql = self.properties(
1203                exp.Properties(
1204                    expressions=properties_locs[exp.Properties.Location.POST_EXPRESSION]
1205                ),
1206                sep=" ",
1207                prefix=" ",
1208                wrapped=False,
1209            )
1210
1211        concurrently = " CONCURRENTLY" if expression.args.get("concurrently") else ""
1212        exists_sql = " IF NOT EXISTS" if expression.args.get("exists") else ""
1213        no_schema_binding = (
1214            " WITH NO SCHEMA BINDING" if expression.args.get("no_schema_binding") else ""
1215        )
1216
1217        clone = self.sql(expression, "clone")
1218        clone = f" {clone}" if clone else ""
1219
1220        if kind in self.EXPRESSION_PRECEDES_PROPERTIES_CREATABLES:
1221            properties_expression = f"{expression_sql}{properties_sql}"
1222        else:
1223            properties_expression = f"{properties_sql}{expression_sql}"
1224
1225        expression_sql = f"CREATE{modifiers} {kind}{concurrently}{exists_sql} {this}{properties_expression}{postexpression_props_sql}{index_sql}{no_schema_binding}{clone}"
1226        return self.prepend_ctes(expression, expression_sql)
1227
1228    def sequenceproperties_sql(self, expression: exp.SequenceProperties) -> str:
1229        start = self.sql(expression, "start")
1230        start = f"START WITH {start}" if start else ""
1231        increment = self.sql(expression, "increment")
1232        increment = f" INCREMENT BY {increment}" if increment else ""
1233        minvalue = self.sql(expression, "minvalue")
1234        minvalue = f" MINVALUE {minvalue}" if minvalue else ""
1235        maxvalue = self.sql(expression, "maxvalue")
1236        maxvalue = f" MAXVALUE {maxvalue}" if maxvalue else ""
1237        owned = self.sql(expression, "owned")
1238        owned = f" OWNED BY {owned}" if owned else ""
1239
1240        cache = expression.args.get("cache")
1241        if cache is None:
1242            cache_str = ""
1243        elif cache is True:
1244            cache_str = " CACHE"
1245        else:
1246            cache_str = f" CACHE {cache}"
1247
1248        options = self.expressions(expression, key="options", flat=True, sep=" ")
1249        options = f" {options}" if options else ""
1250
1251        return f"{start}{increment}{minvalue}{maxvalue}{cache_str}{options}{owned}".lstrip()
1252
1253    def clone_sql(self, expression: exp.Clone) -> str:
1254        this = self.sql(expression, "this")
1255        shallow = "SHALLOW " if expression.args.get("shallow") else ""
1256        keyword = "COPY" if expression.args.get("copy") and self.SUPPORTS_TABLE_COPY else "CLONE"
1257        return f"{shallow}{keyword} {this}"
1258
1259    def describe_sql(self, expression: exp.Describe) -> str:
1260        style = expression.args.get("style")
1261        style = f" {style}" if style else ""
1262        partition = self.sql(expression, "partition")
1263        partition = f" {partition}" if partition else ""
1264        format = self.sql(expression, "format")
1265        format = f" {format}" if format else ""
1266
1267        return f"DESCRIBE{style}{format} {self.sql(expression, 'this')}{partition}"
1268
1269    def heredoc_sql(self, expression: exp.Heredoc) -> str:
1270        tag = self.sql(expression, "tag")
1271        return f"${tag}${self.sql(expression, 'this')}${tag}$"
1272
1273    def prepend_ctes(self, expression: exp.Expression, sql: str) -> str:
1274        with_ = self.sql(expression, "with")
1275        if with_:
1276            sql = f"{with_}{self.sep()}{sql}"
1277        return sql
1278
1279    def with_sql(self, expression: exp.With) -> str:
1280        sql = self.expressions(expression, flat=True)
1281        recursive = (
1282            "RECURSIVE "
1283            if self.CTE_RECURSIVE_KEYWORD_REQUIRED and expression.args.get("recursive")
1284            else ""
1285        )
1286        search = self.sql(expression, "search")
1287        search = f" {search}" if search else ""
1288
1289        return f"WITH {recursive}{sql}{search}"
1290
1291    def cte_sql(self, expression: exp.CTE) -> str:
1292        alias = expression.args.get("alias")
1293        if alias:
1294            alias.add_comments(expression.pop_comments())
1295
1296        alias_sql = self.sql(expression, "alias")
1297
1298        materialized = expression.args.get("materialized")
1299        if materialized is False:
1300            materialized = "NOT MATERIALIZED "
1301        elif materialized:
1302            materialized = "MATERIALIZED "
1303
1304        return f"{alias_sql} AS {materialized or ''}{self.wrap(expression)}"
1305
1306    def tablealias_sql(self, expression: exp.TableAlias) -> str:
1307        alias = self.sql(expression, "this")
1308        columns = self.expressions(expression, key="columns", flat=True)
1309        columns = f"({columns})" if columns else ""
1310
1311        if columns and not self.SUPPORTS_TABLE_ALIAS_COLUMNS:
1312            columns = ""
1313            self.unsupported("Named columns are not supported in table alias.")
1314
1315        if not alias and not self.dialect.UNNEST_COLUMN_ONLY:
1316            alias = self._next_name()
1317
1318        return f"{alias}{columns}"
1319
1320    def bitstring_sql(self, expression: exp.BitString) -> str:
1321        this = self.sql(expression, "this")
1322        if self.dialect.BIT_START:
1323            return f"{self.dialect.BIT_START}{this}{self.dialect.BIT_END}"
1324        return f"{int(this, 2)}"
1325
1326    def hexstring_sql(
1327        self, expression: exp.HexString, binary_function_repr: t.Optional[str] = None
1328    ) -> str:
1329        this = self.sql(expression, "this")
1330        is_integer_type = expression.args.get("is_integer")
1331
1332        if (is_integer_type and not self.dialect.HEX_STRING_IS_INTEGER_TYPE) or (
1333            not self.dialect.HEX_START and not binary_function_repr
1334        ):
1335            # Integer representation will be returned if:
1336            # - The read dialect treats the hex value as integer literal but not the write
1337            # - The transpilation is not supported (write dialect hasn't set HEX_START or the param flag)
1338            return f"{int(this, 16)}"
1339
1340        if not is_integer_type:
1341            # Read dialect treats the hex value as BINARY/BLOB
1342            if binary_function_repr:
1343                # The write dialect supports the transpilation to its equivalent BINARY/BLOB
1344                return self.func(binary_function_repr, exp.Literal.string(this))
1345            if self.dialect.HEX_STRING_IS_INTEGER_TYPE:
1346                # The write dialect does not support the transpilation, it'll treat the hex value as INTEGER
1347                self.unsupported("Unsupported transpilation from BINARY/BLOB hex string")
1348
1349        return f"{self.dialect.HEX_START}{this}{self.dialect.HEX_END}"
1350
1351    def bytestring_sql(self, expression: exp.ByteString) -> str:
1352        this = self.sql(expression, "this")
1353        if self.dialect.BYTE_START:
1354            return f"{self.dialect.BYTE_START}{this}{self.dialect.BYTE_END}"
1355        return this
1356
1357    def unicodestring_sql(self, expression: exp.UnicodeString) -> str:
1358        this = self.sql(expression, "this")
1359        escape = expression.args.get("escape")
1360
1361        if self.dialect.UNICODE_START:
1362            escape_substitute = r"\\\1"
1363            left_quote, right_quote = self.dialect.UNICODE_START, self.dialect.UNICODE_END
1364        else:
1365            escape_substitute = r"\\u\1"
1366            left_quote, right_quote = self.dialect.QUOTE_START, self.dialect.QUOTE_END
1367
1368        if escape:
1369            escape_pattern = re.compile(rf"{escape.name}(\d+)")
1370            escape_sql = f" UESCAPE {self.sql(escape)}" if self.SUPPORTS_UESCAPE else ""
1371        else:
1372            escape_pattern = ESCAPED_UNICODE_RE
1373            escape_sql = ""
1374
1375        if not self.dialect.UNICODE_START or (escape and not self.SUPPORTS_UESCAPE):
1376            this = escape_pattern.sub(escape_substitute, this)
1377
1378        return f"{left_quote}{this}{right_quote}{escape_sql}"
1379
1380    def rawstring_sql(self, expression: exp.RawString) -> str:
1381        string = expression.this
1382        if "\\" in self.dialect.tokenizer_class.STRING_ESCAPES:
1383            string = string.replace("\\", "\\\\")
1384
1385        string = self.escape_str(string, escape_backslash=False)
1386        return f"{self.dialect.QUOTE_START}{string}{self.dialect.QUOTE_END}"
1387
1388    def datatypeparam_sql(self, expression: exp.DataTypeParam) -> str:
1389        this = self.sql(expression, "this")
1390        specifier = self.sql(expression, "expression")
1391        specifier = f" {specifier}" if specifier and self.DATA_TYPE_SPECIFIERS_ALLOWED else ""
1392        return f"{this}{specifier}"
1393
1394    def datatype_sql(self, expression: exp.DataType) -> str:
1395        nested = ""
1396        values = ""
1397        interior = self.expressions(expression, flat=True)
1398
1399        type_value = expression.this
1400        if type_value == exp.DataType.Type.USERDEFINED and expression.args.get("kind"):
1401            type_sql = self.sql(expression, "kind")
1402        else:
1403            type_sql = (
1404                self.TYPE_MAPPING.get(type_value, type_value.value)
1405                if isinstance(type_value, exp.DataType.Type)
1406                else type_value
1407            )
1408
1409        if interior:
1410            if expression.args.get("nested"):
1411                nested = f"{self.STRUCT_DELIMITER[0]}{interior}{self.STRUCT_DELIMITER[1]}"
1412                if expression.args.get("values") is not None:
1413                    delimiters = ("[", "]") if type_value == exp.DataType.Type.ARRAY else ("(", ")")
1414                    values = self.expressions(expression, key="values", flat=True)
1415                    values = f"{delimiters[0]}{values}{delimiters[1]}"
1416            elif type_value == exp.DataType.Type.INTERVAL:
1417                nested = f" {interior}"
1418            else:
1419                nested = f"({interior})"
1420
1421        type_sql = f"{type_sql}{nested}{values}"
1422        if self.TZ_TO_WITH_TIME_ZONE and type_value in (
1423            exp.DataType.Type.TIMETZ,
1424            exp.DataType.Type.TIMESTAMPTZ,
1425        ):
1426            type_sql = f"{type_sql} WITH TIME ZONE"
1427
1428        return type_sql
1429
1430    def directory_sql(self, expression: exp.Directory) -> str:
1431        local = "LOCAL " if expression.args.get("local") else ""
1432        row_format = self.sql(expression, "row_format")
1433        row_format = f" {row_format}" if row_format else ""
1434        return f"{local}DIRECTORY {self.sql(expression, 'this')}{row_format}"
1435
1436    def delete_sql(self, expression: exp.Delete) -> str:
1437        this = self.sql(expression, "this")
1438        this = f" FROM {this}" if this else ""
1439        using = self.sql(expression, "using")
1440        using = f" USING {using}" if using else ""
1441        cluster = self.sql(expression, "cluster")
1442        cluster = f" {cluster}" if cluster else ""
1443        where = self.sql(expression, "where")
1444        returning = self.sql(expression, "returning")
1445        limit = self.sql(expression, "limit")
1446        tables = self.expressions(expression, key="tables")
1447        tables = f" {tables}" if tables else ""
1448        if self.RETURNING_END:
1449            expression_sql = f"{this}{using}{cluster}{where}{returning}{limit}"
1450        else:
1451            expression_sql = f"{returning}{this}{using}{cluster}{where}{limit}"
1452        return self.prepend_ctes(expression, f"DELETE{tables}{expression_sql}")
1453
1454    def drop_sql(self, expression: exp.Drop) -> str:
1455        this = self.sql(expression, "this")
1456        expressions = self.expressions(expression, flat=True)
1457        expressions = f" ({expressions})" if expressions else ""
1458        kind = expression.args["kind"]
1459        kind = self.dialect.INVERSE_CREATABLE_KIND_MAPPING.get(kind) or kind
1460        exists_sql = " IF EXISTS " if expression.args.get("exists") else " "
1461        concurrently_sql = " CONCURRENTLY" if expression.args.get("concurrently") else ""
1462        on_cluster = self.sql(expression, "cluster")
1463        on_cluster = f" {on_cluster}" if on_cluster else ""
1464        temporary = " TEMPORARY" if expression.args.get("temporary") else ""
1465        materialized = " MATERIALIZED" if expression.args.get("materialized") else ""
1466        cascade = " CASCADE" if expression.args.get("cascade") else ""
1467        constraints = " CONSTRAINTS" if expression.args.get("constraints") else ""
1468        purge = " PURGE" if expression.args.get("purge") else ""
1469        return f"DROP{temporary}{materialized} {kind}{concurrently_sql}{exists_sql}{this}{on_cluster}{expressions}{cascade}{constraints}{purge}"
1470
1471    def set_operation(self, expression: exp.SetOperation) -> str:
1472        op_type = type(expression)
1473        op_name = op_type.key.upper()
1474
1475        distinct = expression.args.get("distinct")
1476        if (
1477            distinct is False
1478            and op_type in (exp.Except, exp.Intersect)
1479            and not self.EXCEPT_INTERSECT_SUPPORT_ALL_CLAUSE
1480        ):
1481            self.unsupported(f"{op_name} ALL is not supported")
1482
1483        default_distinct = self.dialect.SET_OP_DISTINCT_BY_DEFAULT[op_type]
1484
1485        if distinct is None:
1486            distinct = default_distinct
1487            if distinct is None:
1488                self.unsupported(f"{op_name} requires DISTINCT or ALL to be specified")
1489
1490        if distinct is default_distinct:
1491            distinct_or_all = ""
1492        else:
1493            distinct_or_all = " DISTINCT" if distinct else " ALL"
1494
1495        side_kind = " ".join(filter(None, [expression.side, expression.kind]))
1496        side_kind = f"{side_kind} " if side_kind else ""
1497
1498        by_name = " BY NAME" if expression.args.get("by_name") else ""
1499        on = self.expressions(expression, key="on", flat=True)
1500        on = f" ON ({on})" if on else ""
1501
1502        return f"{side_kind}{op_name}{distinct_or_all}{by_name}{on}"
1503
1504    def set_operations(self, expression: exp.SetOperation) -> str:
1505        if not self.SET_OP_MODIFIERS:
1506            limit = expression.args.get("limit")
1507            order = expression.args.get("order")
1508
1509            if limit or order:
1510                select = self._move_ctes_to_top_level(
1511                    exp.subquery(expression, "_l_0", copy=False).select("*", copy=False)
1512                )
1513
1514                if limit:
1515                    select = select.limit(limit.pop(), copy=False)
1516                if order:
1517                    select = select.order_by(order.pop(), copy=False)
1518                return self.sql(select)
1519
1520        sqls: t.List[str] = []
1521        stack: t.List[t.Union[str, exp.Expression]] = [expression]
1522
1523        while stack:
1524            node = stack.pop()
1525
1526            if isinstance(node, exp.SetOperation):
1527                stack.append(node.expression)
1528                stack.append(
1529                    self.maybe_comment(
1530                        self.set_operation(node), comments=node.comments, separated=True
1531                    )
1532                )
1533                stack.append(node.this)
1534            else:
1535                sqls.append(self.sql(node))
1536
1537        this = self.sep().join(sqls)
1538        this = self.query_modifiers(expression, this)
1539        return self.prepend_ctes(expression, this)
1540
1541    def fetch_sql(self, expression: exp.Fetch) -> str:
1542        direction = expression.args.get("direction")
1543        direction = f" {direction}" if direction else ""
1544        count = self.sql(expression, "count")
1545        count = f" {count}" if count else ""
1546        limit_options = self.sql(expression, "limit_options")
1547        limit_options = f"{limit_options}" if limit_options else " ROWS ONLY"
1548        return f"{self.seg('FETCH')}{direction}{count}{limit_options}"
1549
1550    def limitoptions_sql(self, expression: exp.LimitOptions) -> str:
1551        percent = " PERCENT" if expression.args.get("percent") else ""
1552        rows = " ROWS" if expression.args.get("rows") else ""
1553        with_ties = " WITH TIES" if expression.args.get("with_ties") else ""
1554        if not with_ties and rows:
1555            with_ties = " ONLY"
1556        return f"{percent}{rows}{with_ties}"
1557
1558    def filter_sql(self, expression: exp.Filter) -> str:
1559        if self.AGGREGATE_FILTER_SUPPORTED:
1560            this = self.sql(expression, "this")
1561            where = self.sql(expression, "expression").strip()
1562            return f"{this} FILTER({where})"
1563
1564        agg = expression.this
1565        agg_arg = agg.this
1566        cond = expression.expression.this
1567        agg_arg.replace(exp.If(this=cond.copy(), true=agg_arg.copy()))
1568        return self.sql(agg)
1569
1570    def hint_sql(self, expression: exp.Hint) -> str:
1571        if not self.QUERY_HINTS:
1572            self.unsupported("Hints are not supported")
1573            return ""
1574
1575        return f" /*+ {self.expressions(expression, sep=self.QUERY_HINT_SEP).strip()} */"
1576
1577    def indexparameters_sql(self, expression: exp.IndexParameters) -> str:
1578        using = self.sql(expression, "using")
1579        using = f" USING {using}" if using else ""
1580        columns = self.expressions(expression, key="columns", flat=True)
1581        columns = f"({columns})" if columns else ""
1582        partition_by = self.expressions(expression, key="partition_by", flat=True)
1583        partition_by = f" PARTITION BY {partition_by}" if partition_by else ""
1584        where = self.sql(expression, "where")
1585        include = self.expressions(expression, key="include", flat=True)
1586        if include:
1587            include = f" INCLUDE ({include})"
1588        with_storage = self.expressions(expression, key="with_storage", flat=True)
1589        with_storage = f" WITH ({with_storage})" if with_storage else ""
1590        tablespace = self.sql(expression, "tablespace")
1591        tablespace = f" USING INDEX TABLESPACE {tablespace}" if tablespace else ""
1592        on = self.sql(expression, "on")
1593        on = f" ON {on}" if on else ""
1594
1595        return f"{using}{columns}{include}{with_storage}{tablespace}{partition_by}{where}{on}"
1596
1597    def index_sql(self, expression: exp.Index) -> str:
1598        unique = "UNIQUE " if expression.args.get("unique") else ""
1599        primary = "PRIMARY " if expression.args.get("primary") else ""
1600        amp = "AMP " if expression.args.get("amp") else ""
1601        name = self.sql(expression, "this")
1602        name = f"{name} " if name else ""
1603        table = self.sql(expression, "table")
1604        table = f"{self.INDEX_ON} {table}" if table else ""
1605
1606        index = "INDEX " if not table else ""
1607
1608        params = self.sql(expression, "params")
1609        return f"{unique}{primary}{amp}{index}{name}{table}{params}"
1610
1611    def identifier_sql(self, expression: exp.Identifier) -> str:
1612        text = expression.name
1613        lower = text.lower()
1614        text = lower if self.normalize and not expression.quoted else text
1615        text = text.replace(self._identifier_end, self._escaped_identifier_end)
1616        if (
1617            expression.quoted
1618            or self.dialect.can_identify(text, self.identify)
1619            or lower in self.RESERVED_KEYWORDS
1620            or (not self.dialect.IDENTIFIERS_CAN_START_WITH_DIGIT and text[:1].isdigit())
1621        ):
1622            text = f"{self._identifier_start}{text}{self._identifier_end}"
1623        return text
1624
1625    def hex_sql(self, expression: exp.Hex) -> str:
1626        text = self.func(self.HEX_FUNC, self.sql(expression, "this"))
1627        if self.dialect.HEX_LOWERCASE:
1628            text = self.func("LOWER", text)
1629
1630        return text
1631
1632    def lowerhex_sql(self, expression: exp.LowerHex) -> str:
1633        text = self.func(self.HEX_FUNC, self.sql(expression, "this"))
1634        if not self.dialect.HEX_LOWERCASE:
1635            text = self.func("LOWER", text)
1636        return text
1637
1638    def inputoutputformat_sql(self, expression: exp.InputOutputFormat) -> str:
1639        input_format = self.sql(expression, "input_format")
1640        input_format = f"INPUTFORMAT {input_format}" if input_format else ""
1641        output_format = self.sql(expression, "output_format")
1642        output_format = f"OUTPUTFORMAT {output_format}" if output_format else ""
1643        return self.sep().join((input_format, output_format))
1644
1645    def national_sql(self, expression: exp.National, prefix: str = "N") -> str:
1646        string = self.sql(exp.Literal.string(expression.name))
1647        return f"{prefix}{string}"
1648
1649    def partition_sql(self, expression: exp.Partition) -> str:
1650        partition_keyword = "SUBPARTITION" if expression.args.get("subpartition") else "PARTITION"
1651        return f"{partition_keyword}({self.expressions(expression, flat=True)})"
1652
1653    def properties_sql(self, expression: exp.Properties) -> str:
1654        root_properties = []
1655        with_properties = []
1656
1657        for p in expression.expressions:
1658            p_loc = self.PROPERTIES_LOCATION[p.__class__]
1659            if p_loc == exp.Properties.Location.POST_WITH:
1660                with_properties.append(p)
1661            elif p_loc == exp.Properties.Location.POST_SCHEMA:
1662                root_properties.append(p)
1663
1664        root_props = self.root_properties(exp.Properties(expressions=root_properties))
1665        with_props = self.with_properties(exp.Properties(expressions=with_properties))
1666
1667        if root_props and with_props and not self.pretty:
1668            with_props = " " + with_props
1669
1670        return root_props + with_props
1671
1672    def root_properties(self, properties: exp.Properties) -> str:
1673        if properties.expressions:
1674            return self.expressions(properties, indent=False, sep=" ")
1675        return ""
1676
1677    def properties(
1678        self,
1679        properties: exp.Properties,
1680        prefix: str = "",
1681        sep: str = ", ",
1682        suffix: str = "",
1683        wrapped: bool = True,
1684    ) -> str:
1685        if properties.expressions:
1686            expressions = self.expressions(properties, sep=sep, indent=False)
1687            if expressions:
1688                expressions = self.wrap(expressions) if wrapped else expressions
1689                return f"{prefix}{' ' if prefix.strip() else ''}{expressions}{suffix}"
1690        return ""
1691
1692    def with_properties(self, properties: exp.Properties) -> str:
1693        return self.properties(properties, prefix=self.seg(self.WITH_PROPERTIES_PREFIX, sep=""))
1694
1695    def locate_properties(self, properties: exp.Properties) -> t.DefaultDict:
1696        properties_locs = defaultdict(list)
1697        for p in properties.expressions:
1698            p_loc = self.PROPERTIES_LOCATION[p.__class__]
1699            if p_loc != exp.Properties.Location.UNSUPPORTED:
1700                properties_locs[p_loc].append(p)
1701            else:
1702                self.unsupported(f"Unsupported property {p.key}")
1703
1704        return properties_locs
1705
1706    def property_name(self, expression: exp.Property, string_key: bool = False) -> str:
1707        if isinstance(expression.this, exp.Dot):
1708            return self.sql(expression, "this")
1709        return f"'{expression.name}'" if string_key else expression.name
1710
1711    def property_sql(self, expression: exp.Property) -> str:
1712        property_cls = expression.__class__
1713        if property_cls == exp.Property:
1714            return f"{self.property_name(expression)}={self.sql(expression, 'value')}"
1715
1716        property_name = exp.Properties.PROPERTY_TO_NAME.get(property_cls)
1717        if not property_name:
1718            self.unsupported(f"Unsupported property {expression.key}")
1719
1720        return f"{property_name}={self.sql(expression, 'this')}"
1721
1722    def likeproperty_sql(self, expression: exp.LikeProperty) -> str:
1723        if self.SUPPORTS_CREATE_TABLE_LIKE:
1724            options = " ".join(f"{e.name} {self.sql(e, 'value')}" for e in expression.expressions)
1725            options = f" {options}" if options else ""
1726
1727            like = f"LIKE {self.sql(expression, 'this')}{options}"
1728            if self.LIKE_PROPERTY_INSIDE_SCHEMA and not isinstance(expression.parent, exp.Schema):
1729                like = f"({like})"
1730
1731            return like
1732
1733        if expression.expressions:
1734            self.unsupported("Transpilation of LIKE property options is unsupported")
1735
1736        select = exp.select("*").from_(expression.this).limit(0)
1737        return f"AS {self.sql(select)}"
1738
1739    def fallbackproperty_sql(self, expression: exp.FallbackProperty) -> str:
1740        no = "NO " if expression.args.get("no") else ""
1741        protection = " PROTECTION" if expression.args.get("protection") else ""
1742        return f"{no}FALLBACK{protection}"
1743
1744    def journalproperty_sql(self, expression: exp.JournalProperty) -> str:
1745        no = "NO " if expression.args.get("no") else ""
1746        local = expression.args.get("local")
1747        local = f"{local} " if local else ""
1748        dual = "DUAL " if expression.args.get("dual") else ""
1749        before = "BEFORE " if expression.args.get("before") else ""
1750        after = "AFTER " if expression.args.get("after") else ""
1751        return f"{no}{local}{dual}{before}{after}JOURNAL"
1752
1753    def freespaceproperty_sql(self, expression: exp.FreespaceProperty) -> str:
1754        freespace = self.sql(expression, "this")
1755        percent = " PERCENT" if expression.args.get("percent") else ""
1756        return f"FREESPACE={freespace}{percent}"
1757
1758    def checksumproperty_sql(self, expression: exp.ChecksumProperty) -> str:
1759        if expression.args.get("default"):
1760            property = "DEFAULT"
1761        elif expression.args.get("on"):
1762            property = "ON"
1763        else:
1764            property = "OFF"
1765        return f"CHECKSUM={property}"
1766
1767    def mergeblockratioproperty_sql(self, expression: exp.MergeBlockRatioProperty) -> str:
1768        if expression.args.get("no"):
1769            return "NO MERGEBLOCKRATIO"
1770        if expression.args.get("default"):
1771            return "DEFAULT MERGEBLOCKRATIO"
1772
1773        percent = " PERCENT" if expression.args.get("percent") else ""
1774        return f"MERGEBLOCKRATIO={self.sql(expression, 'this')}{percent}"
1775
1776    def datablocksizeproperty_sql(self, expression: exp.DataBlocksizeProperty) -> str:
1777        default = expression.args.get("default")
1778        minimum = expression.args.get("minimum")
1779        maximum = expression.args.get("maximum")
1780        if default or minimum or maximum:
1781            if default:
1782                prop = "DEFAULT"
1783            elif minimum:
1784                prop = "MINIMUM"
1785            else:
1786                prop = "MAXIMUM"
1787            return f"{prop} DATABLOCKSIZE"
1788        units = expression.args.get("units")
1789        units = f" {units}" if units else ""
1790        return f"DATABLOCKSIZE={self.sql(expression, 'size')}{units}"
1791
1792    def blockcompressionproperty_sql(self, expression: exp.BlockCompressionProperty) -> str:
1793        autotemp = expression.args.get("autotemp")
1794        always = expression.args.get("always")
1795        default = expression.args.get("default")
1796        manual = expression.args.get("manual")
1797        never = expression.args.get("never")
1798
1799        if autotemp is not None:
1800            prop = f"AUTOTEMP({self.expressions(autotemp)})"
1801        elif always:
1802            prop = "ALWAYS"
1803        elif default:
1804            prop = "DEFAULT"
1805        elif manual:
1806            prop = "MANUAL"
1807        elif never:
1808            prop = "NEVER"
1809        return f"BLOCKCOMPRESSION={prop}"
1810
1811    def isolatedloadingproperty_sql(self, expression: exp.IsolatedLoadingProperty) -> str:
1812        no = expression.args.get("no")
1813        no = " NO" if no else ""
1814        concurrent = expression.args.get("concurrent")
1815        concurrent = " CONCURRENT" if concurrent else ""
1816        target = self.sql(expression, "target")
1817        target = f" {target}" if target else ""
1818        return f"WITH{no}{concurrent} ISOLATED LOADING{target}"
1819
1820    def partitionboundspec_sql(self, expression: exp.PartitionBoundSpec) -> str:
1821        if isinstance(expression.this, list):
1822            return f"IN ({self.expressions(expression, key='this', flat=True)})"
1823        if expression.this:
1824            modulus = self.sql(expression, "this")
1825            remainder = self.sql(expression, "expression")
1826            return f"WITH (MODULUS {modulus}, REMAINDER {remainder})"
1827
1828        from_expressions = self.expressions(expression, key="from_expressions", flat=True)
1829        to_expressions = self.expressions(expression, key="to_expressions", flat=True)
1830        return f"FROM ({from_expressions}) TO ({to_expressions})"
1831
1832    def partitionedofproperty_sql(self, expression: exp.PartitionedOfProperty) -> str:
1833        this = self.sql(expression, "this")
1834
1835        for_values_or_default = expression.expression
1836        if isinstance(for_values_or_default, exp.PartitionBoundSpec):
1837            for_values_or_default = f" FOR VALUES {self.sql(for_values_or_default)}"
1838        else:
1839            for_values_or_default = " DEFAULT"
1840
1841        return f"PARTITION OF {this}{for_values_or_default}"
1842
1843    def lockingproperty_sql(self, expression: exp.LockingProperty) -> str:
1844        kind = expression.args.get("kind")
1845        this = f" {self.sql(expression, 'this')}" if expression.this else ""
1846        for_or_in = expression.args.get("for_or_in")
1847        for_or_in = f" {for_or_in}" if for_or_in else ""
1848        lock_type = expression.args.get("lock_type")
1849        override = " OVERRIDE" if expression.args.get("override") else ""
1850        return f"LOCKING {kind}{this}{for_or_in} {lock_type}{override}"
1851
1852    def withdataproperty_sql(self, expression: exp.WithDataProperty) -> str:
1853        data_sql = f"WITH {'NO ' if expression.args.get('no') else ''}DATA"
1854        statistics = expression.args.get("statistics")
1855        statistics_sql = ""
1856        if statistics is not None:
1857            statistics_sql = f" AND {'NO ' if not statistics else ''}STATISTICS"
1858        return f"{data_sql}{statistics_sql}"
1859
1860    def withsystemversioningproperty_sql(self, expression: exp.WithSystemVersioningProperty) -> str:
1861        this = self.sql(expression, "this")
1862        this = f"HISTORY_TABLE={this}" if this else ""
1863        data_consistency: t.Optional[str] = self.sql(expression, "data_consistency")
1864        data_consistency = (
1865            f"DATA_CONSISTENCY_CHECK={data_consistency}" if data_consistency else None
1866        )
1867        retention_period: t.Optional[str] = self.sql(expression, "retention_period")
1868        retention_period = (
1869            f"HISTORY_RETENTION_PERIOD={retention_period}" if retention_period else None
1870        )
1871
1872        if this:
1873            on_sql = self.func("ON", this, data_consistency, retention_period)
1874        else:
1875            on_sql = "ON" if expression.args.get("on") else "OFF"
1876
1877        sql = f"SYSTEM_VERSIONING={on_sql}"
1878
1879        return f"WITH({sql})" if expression.args.get("with") else sql
1880
1881    def insert_sql(self, expression: exp.Insert) -> str:
1882        hint = self.sql(expression, "hint")
1883        overwrite = expression.args.get("overwrite")
1884
1885        if isinstance(expression.this, exp.Directory):
1886            this = " OVERWRITE" if overwrite else " INTO"
1887        else:
1888            this = self.INSERT_OVERWRITE if overwrite else " INTO"
1889
1890        stored = self.sql(expression, "stored")
1891        stored = f" {stored}" if stored else ""
1892        alternative = expression.args.get("alternative")
1893        alternative = f" OR {alternative}" if alternative else ""
1894        ignore = " IGNORE" if expression.args.get("ignore") else ""
1895        is_function = expression.args.get("is_function")
1896        if is_function:
1897            this = f"{this} FUNCTION"
1898        this = f"{this} {self.sql(expression, 'this')}"
1899
1900        exists = " IF EXISTS" if expression.args.get("exists") else ""
1901        where = self.sql(expression, "where")
1902        where = f"{self.sep()}REPLACE WHERE {where}" if where else ""
1903        expression_sql = f"{self.sep()}{self.sql(expression, 'expression')}"
1904        on_conflict = self.sql(expression, "conflict")
1905        on_conflict = f" {on_conflict}" if on_conflict else ""
1906        by_name = " BY NAME" if expression.args.get("by_name") else ""
1907        returning = self.sql(expression, "returning")
1908
1909        if self.RETURNING_END:
1910            expression_sql = f"{expression_sql}{on_conflict}{returning}"
1911        else:
1912            expression_sql = f"{returning}{expression_sql}{on_conflict}"
1913
1914        partition_by = self.sql(expression, "partition")
1915        partition_by = f" {partition_by}" if partition_by else ""
1916        settings = self.sql(expression, "settings")
1917        settings = f" {settings}" if settings else ""
1918
1919        source = self.sql(expression, "source")
1920        source = f"TABLE {source}" if source else ""
1921
1922        sql = f"INSERT{hint}{alternative}{ignore}{this}{stored}{by_name}{exists}{partition_by}{settings}{where}{expression_sql}{source}"
1923        return self.prepend_ctes(expression, sql)
1924
1925    def introducer_sql(self, expression: exp.Introducer) -> str:
1926        return f"{self.sql(expression, 'this')} {self.sql(expression, 'expression')}"
1927
1928    def kill_sql(self, expression: exp.Kill) -> str:
1929        kind = self.sql(expression, "kind")
1930        kind = f" {kind}" if kind else ""
1931        this = self.sql(expression, "this")
1932        this = f" {this}" if this else ""
1933        return f"KILL{kind}{this}"
1934
1935    def pseudotype_sql(self, expression: exp.PseudoType) -> str:
1936        return expression.name
1937
1938    def objectidentifier_sql(self, expression: exp.ObjectIdentifier) -> str:
1939        return expression.name
1940
1941    def onconflict_sql(self, expression: exp.OnConflict) -> str:
1942        conflict = "ON DUPLICATE KEY" if expression.args.get("duplicate") else "ON CONFLICT"
1943
1944        constraint = self.sql(expression, "constraint")
1945        constraint = f" ON CONSTRAINT {constraint}" if constraint else ""
1946
1947        conflict_keys = self.expressions(expression, key="conflict_keys", flat=True)
1948        conflict_keys = f"({conflict_keys}) " if conflict_keys else " "
1949        action = self.sql(expression, "action")
1950
1951        expressions = self.expressions(expression, flat=True)
1952        if expressions:
1953            set_keyword = "SET " if self.DUPLICATE_KEY_UPDATE_WITH_SET else ""
1954            expressions = f" {set_keyword}{expressions}"
1955
1956        where = self.sql(expression, "where")
1957        return f"{conflict}{constraint}{conflict_keys}{action}{expressions}{where}"
1958
1959    def returning_sql(self, expression: exp.Returning) -> str:
1960        return f"{self.seg('RETURNING')} {self.expressions(expression, flat=True)}"
1961
1962    def rowformatdelimitedproperty_sql(self, expression: exp.RowFormatDelimitedProperty) -> str:
1963        fields = self.sql(expression, "fields")
1964        fields = f" FIELDS TERMINATED BY {fields}" if fields else ""
1965        escaped = self.sql(expression, "escaped")
1966        escaped = f" ESCAPED BY {escaped}" if escaped else ""
1967        items = self.sql(expression, "collection_items")
1968        items = f" COLLECTION ITEMS TERMINATED BY {items}" if items else ""
1969        keys = self.sql(expression, "map_keys")
1970        keys = f" MAP KEYS TERMINATED BY {keys}" if keys else ""
1971        lines = self.sql(expression, "lines")
1972        lines = f" LINES TERMINATED BY {lines}" if lines else ""
1973        null = self.sql(expression, "null")
1974        null = f" NULL DEFINED AS {null}" if null else ""
1975        return f"ROW FORMAT DELIMITED{fields}{escaped}{items}{keys}{lines}{null}"
1976
1977    def withtablehint_sql(self, expression: exp.WithTableHint) -> str:
1978        return f"WITH ({self.expressions(expression, flat=True)})"
1979
1980    def indextablehint_sql(self, expression: exp.IndexTableHint) -> str:
1981        this = f"{self.sql(expression, 'this')} INDEX"
1982        target = self.sql(expression, "target")
1983        target = f" FOR {target}" if target else ""
1984        return f"{this}{target} ({self.expressions(expression, flat=True)})"
1985
1986    def historicaldata_sql(self, expression: exp.HistoricalData) -> str:
1987        this = self.sql(expression, "this")
1988        kind = self.sql(expression, "kind")
1989        expr = self.sql(expression, "expression")
1990        return f"{this} ({kind} => {expr})"
1991
1992    def table_parts(self, expression: exp.Table) -> str:
1993        return ".".join(
1994            self.sql(part)
1995            for part in (
1996                expression.args.get("catalog"),
1997                expression.args.get("db"),
1998                expression.args.get("this"),
1999            )
2000            if part is not None
2001        )
2002
2003    def table_sql(self, expression: exp.Table, sep: str = " AS ") -> str:
2004        table = self.table_parts(expression)
2005        only = "ONLY " if expression.args.get("only") else ""
2006        partition = self.sql(expression, "partition")
2007        partition = f" {partition}" if partition else ""
2008        version = self.sql(expression, "version")
2009        version = f" {version}" if version else ""
2010        alias = self.sql(expression, "alias")
2011        alias = f"{sep}{alias}" if alias else ""
2012
2013        sample = self.sql(expression, "sample")
2014        if self.dialect.ALIAS_POST_TABLESAMPLE:
2015            sample_pre_alias = sample
2016            sample_post_alias = ""
2017        else:
2018            sample_pre_alias = ""
2019            sample_post_alias = sample
2020
2021        hints = self.expressions(expression, key="hints", sep=" ")
2022        hints = f" {hints}" if hints and self.TABLE_HINTS else ""
2023        pivots = self.expressions(expression, key="pivots", sep="", flat=True)
2024        joins = self.indent(
2025            self.expressions(expression, key="joins", sep="", flat=True), skip_first=True
2026        )
2027        laterals = self.expressions(expression, key="laterals", sep="")
2028
2029        file_format = self.sql(expression, "format")
2030        if file_format:
2031            pattern = self.sql(expression, "pattern")
2032            pattern = f", PATTERN => {pattern}" if pattern else ""
2033            file_format = f" (FILE_FORMAT => {file_format}{pattern})"
2034
2035        ordinality = expression.args.get("ordinality") or ""
2036        if ordinality:
2037            ordinality = f" WITH ORDINALITY{alias}"
2038            alias = ""
2039
2040        when = self.sql(expression, "when")
2041        if when:
2042            table = f"{table} {when}"
2043
2044        changes = self.sql(expression, "changes")
2045        changes = f" {changes}" if changes else ""
2046
2047        rows_from = self.expressions(expression, key="rows_from")
2048        if rows_from:
2049            table = f"ROWS FROM {self.wrap(rows_from)}"
2050
2051        return f"{only}{table}{changes}{partition}{version}{file_format}{sample_pre_alias}{alias}{hints}{pivots}{sample_post_alias}{joins}{laterals}{ordinality}"
2052
2053    def tablefromrows_sql(self, expression: exp.TableFromRows) -> str:
2054        table = self.func("TABLE", expression.this)
2055        alias = self.sql(expression, "alias")
2056        alias = f" AS {alias}" if alias else ""
2057        sample = self.sql(expression, "sample")
2058        pivots = self.expressions(expression, key="pivots", sep="", flat=True)
2059        joins = self.indent(
2060            self.expressions(expression, key="joins", sep="", flat=True), skip_first=True
2061        )
2062        return f"{table}{alias}{pivots}{sample}{joins}"
2063
2064    def tablesample_sql(
2065        self,
2066        expression: exp.TableSample,
2067        tablesample_keyword: t.Optional[str] = None,
2068    ) -> str:
2069        method = self.sql(expression, "method")
2070        method = f"{method} " if method and self.TABLESAMPLE_WITH_METHOD else ""
2071        numerator = self.sql(expression, "bucket_numerator")
2072        denominator = self.sql(expression, "bucket_denominator")
2073        field = self.sql(expression, "bucket_field")
2074        field = f" ON {field}" if field else ""
2075        bucket = f"BUCKET {numerator} OUT OF {denominator}{field}" if numerator else ""
2076        seed = self.sql(expression, "seed")
2077        seed = f" {self.TABLESAMPLE_SEED_KEYWORD} ({seed})" if seed else ""
2078
2079        size = self.sql(expression, "size")
2080        if size and self.TABLESAMPLE_SIZE_IS_ROWS:
2081            size = f"{size} ROWS"
2082
2083        percent = self.sql(expression, "percent")
2084        if percent and not self.dialect.TABLESAMPLE_SIZE_IS_PERCENT:
2085            percent = f"{percent} PERCENT"
2086
2087        expr = f"{bucket}{percent}{size}"
2088        if self.TABLESAMPLE_REQUIRES_PARENS:
2089            expr = f"({expr})"
2090
2091        return f" {tablesample_keyword or self.TABLESAMPLE_KEYWORDS} {method}{expr}{seed}"
2092
2093    def pivot_sql(self, expression: exp.Pivot) -> str:
2094        expressions = self.expressions(expression, flat=True)
2095        direction = "UNPIVOT" if expression.unpivot else "PIVOT"
2096
2097        group = self.sql(expression, "group")
2098
2099        if expression.this:
2100            this = self.sql(expression, "this")
2101            if not expressions:
2102                return f"UNPIVOT {this}"
2103
2104            on = f"{self.seg('ON')} {expressions}"
2105            into = self.sql(expression, "into")
2106            into = f"{self.seg('INTO')} {into}" if into else ""
2107            using = self.expressions(expression, key="using", flat=True)
2108            using = f"{self.seg('USING')} {using}" if using else ""
2109            return f"{direction} {this}{on}{into}{using}{group}"
2110
2111        alias = self.sql(expression, "alias")
2112        alias = f" AS {alias}" if alias else ""
2113
2114        fields = self.expressions(
2115            expression,
2116            "fields",
2117            sep=" ",
2118            dynamic=True,
2119            new_line=True,
2120            skip_first=True,
2121            skip_last=True,
2122        )
2123
2124        include_nulls = expression.args.get("include_nulls")
2125        if include_nulls is not None:
2126            nulls = " INCLUDE NULLS " if include_nulls else " EXCLUDE NULLS "
2127        else:
2128            nulls = ""
2129
2130        default_on_null = self.sql(expression, "default_on_null")
2131        default_on_null = f" DEFAULT ON NULL ({default_on_null})" if default_on_null else ""
2132        return f"{self.seg(direction)}{nulls}({expressions} FOR {fields}{default_on_null}{group}){alias}"
2133
2134    def version_sql(self, expression: exp.Version) -> str:
2135        this = f"FOR {expression.name}"
2136        kind = expression.text("kind")
2137        expr = self.sql(expression, "expression")
2138        return f"{this} {kind} {expr}"
2139
2140    def tuple_sql(self, expression: exp.Tuple) -> str:
2141        return f"({self.expressions(expression, dynamic=True, new_line=True, skip_first=True, skip_last=True)})"
2142
2143    def update_sql(self, expression: exp.Update) -> str:
2144        this = self.sql(expression, "this")
2145        set_sql = self.expressions(expression, flat=True)
2146        from_sql = self.sql(expression, "from")
2147        where_sql = self.sql(expression, "where")
2148        returning = self.sql(expression, "returning")
2149        order = self.sql(expression, "order")
2150        limit = self.sql(expression, "limit")
2151        if self.RETURNING_END:
2152            expression_sql = f"{from_sql}{where_sql}{returning}"
2153        else:
2154            expression_sql = f"{returning}{from_sql}{where_sql}"
2155        sql = f"UPDATE {this} SET {set_sql}{expression_sql}{order}{limit}"
2156        return self.prepend_ctes(expression, sql)
2157
2158    def values_sql(self, expression: exp.Values, values_as_table: bool = True) -> str:
2159        values_as_table = values_as_table and self.VALUES_AS_TABLE
2160
2161        # The VALUES clause is still valid in an `INSERT INTO ..` statement, for example
2162        if values_as_table or not expression.find_ancestor(exp.From, exp.Join):
2163            args = self.expressions(expression)
2164            alias = self.sql(expression, "alias")
2165            values = f"VALUES{self.seg('')}{args}"
2166            values = (
2167                f"({values})"
2168                if self.WRAP_DERIVED_VALUES
2169                and (alias or isinstance(expression.parent, (exp.From, exp.Table)))
2170                else values
2171            )
2172            return f"{values} AS {alias}" if alias else values
2173
2174        # Converts `VALUES...` expression into a series of select unions.
2175        alias_node = expression.args.get("alias")
2176        column_names = alias_node and alias_node.columns
2177
2178        selects: t.List[exp.Query] = []
2179
2180        for i, tup in enumerate(expression.expressions):
2181            row = tup.expressions
2182
2183            if i == 0 and column_names:
2184                row = [
2185                    exp.alias_(value, column_name) for value, column_name in zip(row, column_names)
2186                ]
2187
2188            selects.append(exp.Select(expressions=row))
2189
2190        if self.pretty:
2191            # This may result in poor performance for large-cardinality `VALUES` tables, due to
2192            # the deep nesting of the resulting exp.Unions. If this is a problem, either increase
2193            # `sys.setrecursionlimit` to avoid RecursionErrors, or don't set `pretty`.
2194            query = reduce(lambda x, y: exp.union(x, y, distinct=False, copy=False), selects)
2195            return self.subquery_sql(query.subquery(alias_node and alias_node.this, copy=False))
2196
2197        alias = f" AS {self.sql(alias_node, 'this')}" if alias_node else ""
2198        unions = " UNION ALL ".join(self.sql(select) for select in selects)
2199        return f"({unions}){alias}"
2200
2201    def var_sql(self, expression: exp.Var) -> str:
2202        return self.sql(expression, "this")
2203
2204    @unsupported_args("expressions")
2205    def into_sql(self, expression: exp.Into) -> str:
2206        temporary = " TEMPORARY" if expression.args.get("temporary") else ""
2207        unlogged = " UNLOGGED" if expression.args.get("unlogged") else ""
2208        return f"{self.seg('INTO')}{temporary or unlogged} {self.sql(expression, 'this')}"
2209
2210    def from_sql(self, expression: exp.From) -> str:
2211        return f"{self.seg('FROM')} {self.sql(expression, 'this')}"
2212
2213    def groupingsets_sql(self, expression: exp.GroupingSets) -> str:
2214        grouping_sets = self.expressions(expression, indent=False)
2215        return f"GROUPING SETS {self.wrap(grouping_sets)}"
2216
2217    def rollup_sql(self, expression: exp.Rollup) -> str:
2218        expressions = self.expressions(expression, indent=False)
2219        return f"ROLLUP {self.wrap(expressions)}" if expressions else "WITH ROLLUP"
2220
2221    def cube_sql(self, expression: exp.Cube) -> str:
2222        expressions = self.expressions(expression, indent=False)
2223        return f"CUBE {self.wrap(expressions)}" if expressions else "WITH CUBE"
2224
2225    def group_sql(self, expression: exp.Group) -> str:
2226        group_by_all = expression.args.get("all")
2227        if group_by_all is True:
2228            modifier = " ALL"
2229        elif group_by_all is False:
2230            modifier = " DISTINCT"
2231        else:
2232            modifier = ""
2233
2234        group_by = self.op_expressions(f"GROUP BY{modifier}", expression)
2235
2236        grouping_sets = self.expressions(expression, key="grouping_sets")
2237        cube = self.expressions(expression, key="cube")
2238        rollup = self.expressions(expression, key="rollup")
2239
2240        groupings = csv(
2241            self.seg(grouping_sets) if grouping_sets else "",
2242            self.seg(cube) if cube else "",
2243            self.seg(rollup) if rollup else "",
2244            self.seg("WITH TOTALS") if expression.args.get("totals") else "",
2245            sep=self.GROUPINGS_SEP,
2246        )
2247
2248        if (
2249            expression.expressions
2250            and groupings
2251            and groupings.strip() not in ("WITH CUBE", "WITH ROLLUP")
2252        ):
2253            group_by = f"{group_by}{self.GROUPINGS_SEP}"
2254
2255        return f"{group_by}{groupings}"
2256
2257    def having_sql(self, expression: exp.Having) -> str:
2258        this = self.indent(self.sql(expression, "this"))
2259        return f"{self.seg('HAVING')}{self.sep()}{this}"
2260
2261    def connect_sql(self, expression: exp.Connect) -> str:
2262        start = self.sql(expression, "start")
2263        start = self.seg(f"START WITH {start}") if start else ""
2264        nocycle = " NOCYCLE" if expression.args.get("nocycle") else ""
2265        connect = self.sql(expression, "connect")
2266        connect = self.seg(f"CONNECT BY{nocycle} {connect}")
2267        return start + connect
2268
2269    def prior_sql(self, expression: exp.Prior) -> str:
2270        return f"PRIOR {self.sql(expression, 'this')}"
2271
2272    def join_sql(self, expression: exp.Join) -> str:
2273        if not self.SEMI_ANTI_JOIN_WITH_SIDE and expression.kind in ("SEMI", "ANTI"):
2274            side = None
2275        else:
2276            side = expression.side
2277
2278        op_sql = " ".join(
2279            op
2280            for op in (
2281                expression.method,
2282                "GLOBAL" if expression.args.get("global") else None,
2283                side,
2284                expression.kind,
2285                expression.hint if self.JOIN_HINTS else None,
2286            )
2287            if op
2288        )
2289        match_cond = self.sql(expression, "match_condition")
2290        match_cond = f" MATCH_CONDITION ({match_cond})" if match_cond else ""
2291        on_sql = self.sql(expression, "on")
2292        using = expression.args.get("using")
2293
2294        if not on_sql and using:
2295            on_sql = csv(*(self.sql(column) for column in using))
2296
2297        this = expression.this
2298        this_sql = self.sql(this)
2299
2300        exprs = self.expressions(expression)
2301        if exprs:
2302            this_sql = f"{this_sql},{self.seg(exprs)}"
2303
2304        if on_sql:
2305            on_sql = self.indent(on_sql, skip_first=True)
2306            space = self.seg(" " * self.pad) if self.pretty else " "
2307            if using:
2308                on_sql = f"{space}USING ({on_sql})"
2309            else:
2310                on_sql = f"{space}ON {on_sql}"
2311        elif not op_sql:
2312            if isinstance(this, exp.Lateral) and this.args.get("cross_apply") is not None:
2313                return f" {this_sql}"
2314
2315            return f", {this_sql}"
2316
2317        if op_sql != "STRAIGHT_JOIN":
2318            op_sql = f"{op_sql} JOIN" if op_sql else "JOIN"
2319
2320        pivots = self.expressions(expression, key="pivots", sep="", flat=True)
2321        return f"{self.seg(op_sql)} {this_sql}{match_cond}{on_sql}{pivots}"
2322
2323    def lambda_sql(self, expression: exp.Lambda, arrow_sep: str = "->", wrap: bool = True) -> str:
2324        args = self.expressions(expression, flat=True)
2325        args = f"({args})" if wrap and len(args.split(",")) > 1 else args
2326        return f"{args} {arrow_sep} {self.sql(expression, 'this')}"
2327
2328    def lateral_op(self, expression: exp.Lateral) -> str:
2329        cross_apply = expression.args.get("cross_apply")
2330
2331        # https://www.mssqltips.com/sqlservertip/1958/sql-server-cross-apply-and-outer-apply/
2332        if cross_apply is True:
2333            op = "INNER JOIN "
2334        elif cross_apply is False:
2335            op = "LEFT JOIN "
2336        else:
2337            op = ""
2338
2339        return f"{op}LATERAL"
2340
2341    def lateral_sql(self, expression: exp.Lateral) -> str:
2342        this = self.sql(expression, "this")
2343
2344        if expression.args.get("view"):
2345            alias = expression.args["alias"]
2346            columns = self.expressions(alias, key="columns", flat=True)
2347            table = f" {alias.name}" if alias.name else ""
2348            columns = f" AS {columns}" if columns else ""
2349            op_sql = self.seg(f"LATERAL VIEW{' OUTER' if expression.args.get('outer') else ''}")
2350            return f"{op_sql}{self.sep()}{this}{table}{columns}"
2351
2352        alias = self.sql(expression, "alias")
2353        alias = f" AS {alias}" if alias else ""
2354
2355        ordinality = expression.args.get("ordinality") or ""
2356        if ordinality:
2357            ordinality = f" WITH ORDINALITY{alias}"
2358            alias = ""
2359
2360        return f"{self.lateral_op(expression)} {this}{alias}{ordinality}"
2361
2362    def limit_sql(self, expression: exp.Limit, top: bool = False) -> str:
2363        this = self.sql(expression, "this")
2364
2365        args = [
2366            self._simplify_unless_literal(e) if self.LIMIT_ONLY_LITERALS else e
2367            for e in (expression.args.get(k) for k in ("offset", "expression"))
2368            if e
2369        ]
2370
2371        args_sql = ", ".join(self.sql(e) for e in args)
2372        args_sql = f"({args_sql})" if top and any(not e.is_number for e in args) else args_sql
2373        expressions = self.expressions(expression, flat=True)
2374        limit_options = self.sql(expression, "limit_options")
2375        expressions = f" BY {expressions}" if expressions else ""
2376
2377        return f"{this}{self.seg('TOP' if top else 'LIMIT')} {args_sql}{limit_options}{expressions}"
2378
2379    def offset_sql(self, expression: exp.Offset) -> str:
2380        this = self.sql(expression, "this")
2381        value = expression.expression
2382        value = self._simplify_unless_literal(value) if self.LIMIT_ONLY_LITERALS else value
2383        expressions = self.expressions(expression, flat=True)
2384        expressions = f" BY {expressions}" if expressions else ""
2385        return f"{this}{self.seg('OFFSET')} {self.sql(value)}{expressions}"
2386
2387    def setitem_sql(self, expression: exp.SetItem) -> str:
2388        kind = self.sql(expression, "kind")
2389        kind = f"{kind} " if kind else ""
2390        this = self.sql(expression, "this")
2391        expressions = self.expressions(expression)
2392        collate = self.sql(expression, "collate")
2393        collate = f" COLLATE {collate}" if collate else ""
2394        global_ = "GLOBAL " if expression.args.get("global") else ""
2395        return f"{global_}{kind}{this}{expressions}{collate}"
2396
2397    def set_sql(self, expression: exp.Set) -> str:
2398        expressions = f" {self.expressions(expression, flat=True)}"
2399        tag = " TAG" if expression.args.get("tag") else ""
2400        return f"{'UNSET' if expression.args.get('unset') else 'SET'}{tag}{expressions}"
2401
2402    def pragma_sql(self, expression: exp.Pragma) -> str:
2403        return f"PRAGMA {self.sql(expression, 'this')}"
2404
2405    def lock_sql(self, expression: exp.Lock) -> str:
2406        if not self.LOCKING_READS_SUPPORTED:
2407            self.unsupported("Locking reads using 'FOR UPDATE/SHARE' are not supported")
2408            return ""
2409
2410        update = expression.args["update"]
2411        key = expression.args.get("key")
2412        if update:
2413            lock_type = "FOR NO KEY UPDATE" if key else "FOR UPDATE"
2414        else:
2415            lock_type = "FOR KEY SHARE" if key else "FOR SHARE"
2416        expressions = self.expressions(expression, flat=True)
2417        expressions = f" OF {expressions}" if expressions else ""
2418        wait = expression.args.get("wait")
2419
2420        if wait is not None:
2421            if isinstance(wait, exp.Literal):
2422                wait = f" WAIT {self.sql(wait)}"
2423            else:
2424                wait = " NOWAIT" if wait else " SKIP LOCKED"
2425
2426        return f"{lock_type}{expressions}{wait or ''}"
2427
2428    def literal_sql(self, expression: exp.Literal) -> str:
2429        text = expression.this or ""
2430        if expression.is_string:
2431            text = f"{self.dialect.QUOTE_START}{self.escape_str(text)}{self.dialect.QUOTE_END}"
2432        return text
2433
2434    def escape_str(self, text: str, escape_backslash: bool = True) -> str:
2435        if self.dialect.ESCAPED_SEQUENCES:
2436            to_escaped = self.dialect.ESCAPED_SEQUENCES
2437            text = "".join(
2438                to_escaped.get(ch, ch) if escape_backslash or ch != "\\" else ch for ch in text
2439            )
2440
2441        return self._replace_line_breaks(text).replace(
2442            self.dialect.QUOTE_END, self._escaped_quote_end
2443        )
2444
2445    def loaddata_sql(self, expression: exp.LoadData) -> str:
2446        local = " LOCAL" if expression.args.get("local") else ""
2447        inpath = f" INPATH {self.sql(expression, 'inpath')}"
2448        overwrite = " OVERWRITE" if expression.args.get("overwrite") else ""
2449        this = f" INTO TABLE {self.sql(expression, 'this')}"
2450        partition = self.sql(expression, "partition")
2451        partition = f" {partition}" if partition else ""
2452        input_format = self.sql(expression, "input_format")
2453        input_format = f" INPUTFORMAT {input_format}" if input_format else ""
2454        serde = self.sql(expression, "serde")
2455        serde = f" SERDE {serde}" if serde else ""
2456        return f"LOAD DATA{local}{inpath}{overwrite}{this}{partition}{input_format}{serde}"
2457
2458    def null_sql(self, *_) -> str:
2459        return "NULL"
2460
2461    def boolean_sql(self, expression: exp.Boolean) -> str:
2462        return "TRUE" if expression.this else "FALSE"
2463
2464    def order_sql(self, expression: exp.Order, flat: bool = False) -> str:
2465        this = self.sql(expression, "this")
2466        this = f"{this} " if this else this
2467        siblings = "SIBLINGS " if expression.args.get("siblings") else ""
2468        return self.op_expressions(f"{this}ORDER {siblings}BY", expression, flat=this or flat)  # type: ignore
2469
2470    def withfill_sql(self, expression: exp.WithFill) -> str:
2471        from_sql = self.sql(expression, "from")
2472        from_sql = f" FROM {from_sql}" if from_sql else ""
2473        to_sql = self.sql(expression, "to")
2474        to_sql = f" TO {to_sql}" if to_sql else ""
2475        step_sql = self.sql(expression, "step")
2476        step_sql = f" STEP {step_sql}" if step_sql else ""
2477        interpolated_values = [
2478            f"{self.sql(e, 'alias')} AS {self.sql(e, 'this')}"
2479            if isinstance(e, exp.Alias)
2480            else self.sql(e, "this")
2481            for e in expression.args.get("interpolate") or []
2482        ]
2483        interpolate = (
2484            f" INTERPOLATE ({', '.join(interpolated_values)})" if interpolated_values else ""
2485        )
2486        return f"WITH FILL{from_sql}{to_sql}{step_sql}{interpolate}"
2487
2488    def cluster_sql(self, expression: exp.Cluster) -> str:
2489        return self.op_expressions("CLUSTER BY", expression)
2490
2491    def distribute_sql(self, expression: exp.Distribute) -> str:
2492        return self.op_expressions("DISTRIBUTE BY", expression)
2493
2494    def sort_sql(self, expression: exp.Sort) -> str:
2495        return self.op_expressions("SORT BY", expression)
2496
2497    def ordered_sql(self, expression: exp.Ordered) -> str:
2498        desc = expression.args.get("desc")
2499        asc = not desc
2500
2501        nulls_first = expression.args.get("nulls_first")
2502        nulls_last = not nulls_first
2503        nulls_are_large = self.dialect.NULL_ORDERING == "nulls_are_large"
2504        nulls_are_small = self.dialect.NULL_ORDERING == "nulls_are_small"
2505        nulls_are_last = self.dialect.NULL_ORDERING == "nulls_are_last"
2506
2507        this = self.sql(expression, "this")
2508
2509        sort_order = " DESC" if desc else (" ASC" if desc is False else "")
2510        nulls_sort_change = ""
2511        if nulls_first and (
2512            (asc and nulls_are_large) or (desc and nulls_are_small) or nulls_are_last
2513        ):
2514            nulls_sort_change = " NULLS FIRST"
2515        elif (
2516            nulls_last
2517            and ((asc and nulls_are_small) or (desc and nulls_are_large))
2518            and not nulls_are_last
2519        ):
2520            nulls_sort_change = " NULLS LAST"
2521
2522        # If the NULLS FIRST/LAST clause is unsupported, we add another sort key to simulate it
2523        if nulls_sort_change and not self.NULL_ORDERING_SUPPORTED:
2524            window = expression.find_ancestor(exp.Window, exp.Select)
2525            if isinstance(window, exp.Window) and window.args.get("spec"):
2526                self.unsupported(
2527                    f"'{nulls_sort_change.strip()}' translation not supported in window functions"
2528                )
2529                nulls_sort_change = ""
2530            elif self.NULL_ORDERING_SUPPORTED is False and (
2531                (asc and nulls_sort_change == " NULLS LAST")
2532                or (desc and nulls_sort_change == " NULLS FIRST")
2533            ):
2534                # BigQuery does not allow these ordering/nulls combinations when used under
2535                # an aggregation func or under a window containing one
2536                ancestor = expression.find_ancestor(exp.AggFunc, exp.Window, exp.Select)
2537
2538                if isinstance(ancestor, exp.Window):
2539                    ancestor = ancestor.this
2540                if isinstance(ancestor, exp.AggFunc):
2541                    self.unsupported(
2542                        f"'{nulls_sort_change.strip()}' translation not supported for aggregate functions with {sort_order} sort order"
2543                    )
2544                    nulls_sort_change = ""
2545            elif self.NULL_ORDERING_SUPPORTED is None:
2546                if expression.this.is_int:
2547                    self.unsupported(
2548                        f"'{nulls_sort_change.strip()}' translation not supported with positional ordering"
2549                    )
2550                elif not isinstance(expression.this, exp.Rand):
2551                    null_sort_order = " DESC" if nulls_sort_change == " NULLS FIRST" else ""
2552                    this = f"CASE WHEN {this} IS NULL THEN 1 ELSE 0 END{null_sort_order}, {this}"
2553                nulls_sort_change = ""
2554
2555        with_fill = self.sql(expression, "with_fill")
2556        with_fill = f" {with_fill}" if with_fill else ""
2557
2558        return f"{this}{sort_order}{nulls_sort_change}{with_fill}"
2559
2560    def matchrecognizemeasure_sql(self, expression: exp.MatchRecognizeMeasure) -> str:
2561        window_frame = self.sql(expression, "window_frame")
2562        window_frame = f"{window_frame} " if window_frame else ""
2563
2564        this = self.sql(expression, "this")
2565
2566        return f"{window_frame}{this}"
2567
2568    def matchrecognize_sql(self, expression: exp.MatchRecognize) -> str:
2569        partition = self.partition_by_sql(expression)
2570        order = self.sql(expression, "order")
2571        measures = self.expressions(expression, key="measures")
2572        measures = self.seg(f"MEASURES{self.seg(measures)}") if measures else ""
2573        rows = self.sql(expression, "rows")
2574        rows = self.seg(rows) if rows else ""
2575        after = self.sql(expression, "after")
2576        after = self.seg(after) if after else ""
2577        pattern = self.sql(expression, "pattern")
2578        pattern = self.seg(f"PATTERN ({pattern})") if pattern else ""
2579        definition_sqls = [
2580            f"{self.sql(definition, 'alias')} AS {self.sql(definition, 'this')}"
2581            for definition in expression.args.get("define", [])
2582        ]
2583        definitions = self.expressions(sqls=definition_sqls)
2584        define = self.seg(f"DEFINE{self.seg(definitions)}") if definitions else ""
2585        body = "".join(
2586            (
2587                partition,
2588                order,
2589                measures,
2590                rows,
2591                after,
2592                pattern,
2593                define,
2594            )
2595        )
2596        alias = self.sql(expression, "alias")
2597        alias = f" {alias}" if alias else ""
2598        return f"{self.seg('MATCH_RECOGNIZE')} {self.wrap(body)}{alias}"
2599
2600    def query_modifiers(self, expression: exp.Expression, *sqls: str) -> str:
2601        limit = expression.args.get("limit")
2602
2603        if self.LIMIT_FETCH == "LIMIT" and isinstance(limit, exp.Fetch):
2604            limit = exp.Limit(expression=exp.maybe_copy(limit.args.get("count")))
2605        elif self.LIMIT_FETCH == "FETCH" and isinstance(limit, exp.Limit):
2606            limit = exp.Fetch(direction="FIRST", count=exp.maybe_copy(limit.expression))
2607
2608        return csv(
2609            *sqls,
2610            *[self.sql(join) for join in expression.args.get("joins") or []],
2611            self.sql(expression, "match"),
2612            *[self.sql(lateral) for lateral in expression.args.get("laterals") or []],
2613            self.sql(expression, "prewhere"),
2614            self.sql(expression, "where"),
2615            self.sql(expression, "connect"),
2616            self.sql(expression, "group"),
2617            self.sql(expression, "having"),
2618            *[gen(self, expression) for gen in self.AFTER_HAVING_MODIFIER_TRANSFORMS.values()],
2619            self.sql(expression, "order"),
2620            *self.offset_limit_modifiers(expression, isinstance(limit, exp.Fetch), limit),
2621            *self.after_limit_modifiers(expression),
2622            self.options_modifier(expression),
2623            self.for_modifiers(expression),
2624            sep="",
2625        )
2626
2627    def options_modifier(self, expression: exp.Expression) -> str:
2628        options = self.expressions(expression, key="options")
2629        return f" {options}" if options else ""
2630
2631    def for_modifiers(self, expression: exp.Expression) -> str:
2632        for_modifiers = self.expressions(expression, key="for")
2633        return f"{self.sep()}FOR XML{self.seg(for_modifiers)}" if for_modifiers else ""
2634
2635    def queryoption_sql(self, expression: exp.QueryOption) -> str:
2636        self.unsupported("Unsupported query option.")
2637        return ""
2638
2639    def offset_limit_modifiers(
2640        self, expression: exp.Expression, fetch: bool, limit: t.Optional[exp.Fetch | exp.Limit]
2641    ) -> t.List[str]:
2642        return [
2643            self.sql(expression, "offset") if fetch else self.sql(limit),
2644            self.sql(limit) if fetch else self.sql(expression, "offset"),
2645        ]
2646
2647    def after_limit_modifiers(self, expression: exp.Expression) -> t.List[str]:
2648        locks = self.expressions(expression, key="locks", sep=" ")
2649        locks = f" {locks}" if locks else ""
2650        return [locks, self.sql(expression, "sample")]
2651
2652    def select_sql(self, expression: exp.Select) -> str:
2653        into = expression.args.get("into")
2654        if not self.SUPPORTS_SELECT_INTO and into:
2655            into.pop()
2656
2657        hint = self.sql(expression, "hint")
2658        distinct = self.sql(expression, "distinct")
2659        distinct = f" {distinct}" if distinct else ""
2660        kind = self.sql(expression, "kind")
2661
2662        limit = expression.args.get("limit")
2663        if isinstance(limit, exp.Limit) and self.LIMIT_IS_TOP:
2664            top = self.limit_sql(limit, top=True)
2665            limit.pop()
2666        else:
2667            top = ""
2668
2669        expressions = self.expressions(expression)
2670
2671        if kind:
2672            if kind in self.SELECT_KINDS:
2673                kind = f" AS {kind}"
2674            else:
2675                if kind == "STRUCT":
2676                    expressions = self.expressions(
2677                        sqls=[
2678                            self.sql(
2679                                exp.Struct(
2680                                    expressions=[
2681                                        exp.PropertyEQ(this=e.args.get("alias"), expression=e.this)
2682                                        if isinstance(e, exp.Alias)
2683                                        else e
2684                                        for e in expression.expressions
2685                                    ]
2686                                )
2687                            )
2688                        ]
2689                    )
2690                kind = ""
2691
2692        operation_modifiers = self.expressions(expression, key="operation_modifiers", sep=" ")
2693        operation_modifiers = f"{self.sep()}{operation_modifiers}" if operation_modifiers else ""
2694
2695        # We use LIMIT_IS_TOP as a proxy for whether DISTINCT should go first because tsql and Teradata
2696        # are the only dialects that use LIMIT_IS_TOP and both place DISTINCT first.
2697        top_distinct = f"{distinct}{hint}{top}" if self.LIMIT_IS_TOP else f"{top}{hint}{distinct}"
2698        expressions = f"{self.sep()}{expressions}" if expressions else expressions
2699        sql = self.query_modifiers(
2700            expression,
2701            f"SELECT{top_distinct}{operation_modifiers}{kind}{expressions}",
2702            self.sql(expression, "into", comment=False),
2703            self.sql(expression, "from", comment=False),
2704        )
2705
2706        # If both the CTE and SELECT clauses have comments, generate the latter earlier
2707        if expression.args.get("with"):
2708            sql = self.maybe_comment(sql, expression)
2709            expression.pop_comments()
2710
2711        sql = self.prepend_ctes(expression, sql)
2712
2713        if not self.SUPPORTS_SELECT_INTO and into:
2714            if into.args.get("temporary"):
2715                table_kind = " TEMPORARY"
2716            elif self.SUPPORTS_UNLOGGED_TABLES and into.args.get("unlogged"):
2717                table_kind = " UNLOGGED"
2718            else:
2719                table_kind = ""
2720            sql = f"CREATE{table_kind} TABLE {self.sql(into.this)} AS {sql}"
2721
2722        return sql
2723
2724    def schema_sql(self, expression: exp.Schema) -> str:
2725        this = self.sql(expression, "this")
2726        sql = self.schema_columns_sql(expression)
2727        return f"{this} {sql}" if this and sql else this or sql
2728
2729    def schema_columns_sql(self, expression: exp.Schema) -> str:
2730        if expression.expressions:
2731            return f"({self.sep('')}{self.expressions(expression)}{self.seg(')', sep='')}"
2732        return ""
2733
2734    def star_sql(self, expression: exp.Star) -> str:
2735        except_ = self.expressions(expression, key="except", flat=True)
2736        except_ = f"{self.seg(self.STAR_EXCEPT)} ({except_})" if except_ else ""
2737        replace = self.expressions(expression, key="replace", flat=True)
2738        replace = f"{self.seg('REPLACE')} ({replace})" if replace else ""
2739        rename = self.expressions(expression, key="rename", flat=True)
2740        rename = f"{self.seg('RENAME')} ({rename})" if rename else ""
2741        return f"*{except_}{replace}{rename}"
2742
2743    def parameter_sql(self, expression: exp.Parameter) -> str:
2744        this = self.sql(expression, "this")
2745        return f"{self.PARAMETER_TOKEN}{this}"
2746
2747    def sessionparameter_sql(self, expression: exp.SessionParameter) -> str:
2748        this = self.sql(expression, "this")
2749        kind = expression.text("kind")
2750        if kind:
2751            kind = f"{kind}."
2752        return f"@@{kind}{this}"
2753
2754    def placeholder_sql(self, expression: exp.Placeholder) -> str:
2755        return f"{self.NAMED_PLACEHOLDER_TOKEN}{expression.name}" if expression.this else "?"
2756
2757    def subquery_sql(self, expression: exp.Subquery, sep: str = " AS ") -> str:
2758        alias = self.sql(expression, "alias")
2759        alias = f"{sep}{alias}" if alias else ""
2760        sample = self.sql(expression, "sample")
2761        if self.dialect.ALIAS_POST_TABLESAMPLE and sample:
2762            alias = f"{sample}{alias}"
2763
2764            # Set to None so it's not generated again by self.query_modifiers()
2765            expression.set("sample", None)
2766
2767        pivots = self.expressions(expression, key="pivots", sep="", flat=True)
2768        sql = self.query_modifiers(expression, self.wrap(expression), alias, pivots)
2769        return self.prepend_ctes(expression, sql)
2770
2771    def qualify_sql(self, expression: exp.Qualify) -> str:
2772        this = self.indent(self.sql(expression, "this"))
2773        return f"{self.seg('QUALIFY')}{self.sep()}{this}"
2774
2775    def unnest_sql(self, expression: exp.Unnest) -> str:
2776        args = self.expressions(expression, flat=True)
2777
2778        alias = expression.args.get("alias")
2779        offset = expression.args.get("offset")
2780
2781        if self.UNNEST_WITH_ORDINALITY:
2782            if alias and isinstance(offset, exp.Expression):
2783                alias.append("columns", offset)
2784
2785        if alias and self.dialect.UNNEST_COLUMN_ONLY:
2786            columns = alias.columns
2787            alias = self.sql(columns[0]) if columns else ""
2788        else:
2789            alias = self.sql(alias)
2790
2791        alias = f" AS {alias}" if alias else alias
2792        if self.UNNEST_WITH_ORDINALITY:
2793            suffix = f" WITH ORDINALITY{alias}" if offset else alias
2794        else:
2795            if isinstance(offset, exp.Expression):
2796                suffix = f"{alias} WITH OFFSET AS {self.sql(offset)}"
2797            elif offset:
2798                suffix = f"{alias} WITH OFFSET"
2799            else:
2800                suffix = alias
2801
2802        return f"UNNEST({args}){suffix}"
2803
2804    def prewhere_sql(self, expression: exp.PreWhere) -> str:
2805        return ""
2806
2807    def where_sql(self, expression: exp.Where) -> str:
2808        this = self.indent(self.sql(expression, "this"))
2809        return f"{self.seg('WHERE')}{self.sep()}{this}"
2810
2811    def window_sql(self, expression: exp.Window) -> str:
2812        this = self.sql(expression, "this")
2813        partition = self.partition_by_sql(expression)
2814        order = expression.args.get("order")
2815        order = self.order_sql(order, flat=True) if order else ""
2816        spec = self.sql(expression, "spec")
2817        alias = self.sql(expression, "alias")
2818        over = self.sql(expression, "over") or "OVER"
2819
2820        this = f"{this} {'AS' if expression.arg_key == 'windows' else over}"
2821
2822        first = expression.args.get("first")
2823        if first is None:
2824            first = ""
2825        else:
2826            first = "FIRST" if first else "LAST"
2827
2828        if not partition and not order and not spec and alias:
2829            return f"{this} {alias}"
2830
2831        args = self.format_args(
2832            *[arg for arg in (alias, first, partition, order, spec) if arg], sep=" "
2833        )
2834        return f"{this} ({args})"
2835
2836    def partition_by_sql(self, expression: exp.Window | exp.MatchRecognize) -> str:
2837        partition = self.expressions(expression, key="partition_by", flat=True)
2838        return f"PARTITION BY {partition}" if partition else ""
2839
2840    def windowspec_sql(self, expression: exp.WindowSpec) -> str:
2841        kind = self.sql(expression, "kind")
2842        start = csv(self.sql(expression, "start"), self.sql(expression, "start_side"), sep=" ")
2843        end = (
2844            csv(self.sql(expression, "end"), self.sql(expression, "end_side"), sep=" ")
2845            or "CURRENT ROW"
2846        )
2847
2848        window_spec = f"{kind} BETWEEN {start} AND {end}"
2849
2850        exclude = self.sql(expression, "exclude")
2851        if exclude:
2852            if self.SUPPORTS_WINDOW_EXCLUDE:
2853                window_spec += f" EXCLUDE {exclude}"
2854            else:
2855                self.unsupported("EXCLUDE clause is not supported in the WINDOW clause")
2856
2857        return window_spec
2858
2859    def withingroup_sql(self, expression: exp.WithinGroup) -> str:
2860        this = self.sql(expression, "this")
2861        expression_sql = self.sql(expression, "expression")[1:]  # order has a leading space
2862        return f"{this} WITHIN GROUP ({expression_sql})"
2863
2864    def between_sql(self, expression: exp.Between) -> str:
2865        this = self.sql(expression, "this")
2866        low = self.sql(expression, "low")
2867        high = self.sql(expression, "high")
2868        return f"{this} BETWEEN {low} AND {high}"
2869
2870    def bracket_offset_expressions(
2871        self, expression: exp.Bracket, index_offset: t.Optional[int] = None
2872    ) -> t.List[exp.Expression]:
2873        return apply_index_offset(
2874            expression.this,
2875            expression.expressions,
2876            (index_offset or self.dialect.INDEX_OFFSET) - expression.args.get("offset", 0),
2877            dialect=self.dialect,
2878        )
2879
2880    def bracket_sql(self, expression: exp.Bracket) -> str:
2881        expressions = self.bracket_offset_expressions(expression)
2882        expressions_sql = ", ".join(self.sql(e) for e in expressions)
2883        return f"{self.sql(expression, 'this')}[{expressions_sql}]"
2884
2885    def all_sql(self, expression: exp.All) -> str:
2886        return f"ALL {self.wrap(expression)}"
2887
2888    def any_sql(self, expression: exp.Any) -> str:
2889        this = self.sql(expression, "this")
2890        if isinstance(expression.this, (*exp.UNWRAPPED_QUERIES, exp.Paren)):
2891            if isinstance(expression.this, exp.UNWRAPPED_QUERIES):
2892                this = self.wrap(this)
2893            return f"ANY{this}"
2894        return f"ANY {this}"
2895
2896    def exists_sql(self, expression: exp.Exists) -> str:
2897        return f"EXISTS{self.wrap(expression)}"
2898
2899    def case_sql(self, expression: exp.Case) -> str:
2900        this = self.sql(expression, "this")
2901        statements = [f"CASE {this}" if this else "CASE"]
2902
2903        for e in expression.args["ifs"]:
2904            statements.append(f"WHEN {self.sql(e, 'this')}")
2905            statements.append(f"THEN {self.sql(e, 'true')}")
2906
2907        default = self.sql(expression, "default")
2908
2909        if default:
2910            statements.append(f"ELSE {default}")
2911
2912        statements.append("END")
2913
2914        if self.pretty and self.too_wide(statements):
2915            return self.indent("\n".join(statements), skip_first=True, skip_last=True)
2916
2917        return " ".join(statements)
2918
2919    def constraint_sql(self, expression: exp.Constraint) -> str:
2920        this = self.sql(expression, "this")
2921        expressions = self.expressions(expression, flat=True)
2922        return f"CONSTRAINT {this} {expressions}"
2923
2924    def nextvaluefor_sql(self, expression: exp.NextValueFor) -> str:
2925        order = expression.args.get("order")
2926        order = f" OVER ({self.order_sql(order, flat=True)})" if order else ""
2927        return f"NEXT VALUE FOR {self.sql(expression, 'this')}{order}"
2928
2929    def extract_sql(self, expression: exp.Extract) -> str:
2930        from sqlglot.dialects.dialect import map_date_part
2931
2932        this = (
2933            map_date_part(expression.this, self.dialect)
2934            if self.NORMALIZE_EXTRACT_DATE_PARTS
2935            else expression.this
2936        )
2937        this_sql = self.sql(this) if self.EXTRACT_ALLOWS_QUOTES else this.name
2938        expression_sql = self.sql(expression, "expression")
2939
2940        return f"EXTRACT({this_sql} FROM {expression_sql})"
2941
2942    def trim_sql(self, expression: exp.Trim) -> str:
2943        trim_type = self.sql(expression, "position")
2944
2945        if trim_type == "LEADING":
2946            func_name = "LTRIM"
2947        elif trim_type == "TRAILING":
2948            func_name = "RTRIM"
2949        else:
2950            func_name = "TRIM"
2951
2952        return self.func(func_name, expression.this, expression.expression)
2953
2954    def convert_concat_args(self, expression: exp.Concat | exp.ConcatWs) -> t.List[exp.Expression]:
2955        args = expression.expressions
2956        if isinstance(expression, exp.ConcatWs):
2957            args = args[1:]  # Skip the delimiter
2958
2959        if self.dialect.STRICT_STRING_CONCAT and expression.args.get("safe"):
2960            args = [exp.cast(e, exp.DataType.Type.TEXT) for e in args]
2961
2962        if not self.dialect.CONCAT_COALESCE and expression.args.get("coalesce"):
2963            args = [exp.func("coalesce", e, exp.Literal.string("")) for e in args]
2964
2965        return args
2966
2967    def concat_sql(self, expression: exp.Concat) -> str:
2968        expressions = self.convert_concat_args(expression)
2969
2970        # Some dialects don't allow a single-argument CONCAT call
2971        if not self.SUPPORTS_SINGLE_ARG_CONCAT and len(expressions) == 1:
2972            return self.sql(expressions[0])
2973
2974        return self.func("CONCAT", *expressions)
2975
2976    def concatws_sql(self, expression: exp.ConcatWs) -> str:
2977        return self.func(
2978            "CONCAT_WS", seq_get(expression.expressions, 0), *self.convert_concat_args(expression)
2979        )
2980
2981    def check_sql(self, expression: exp.Check) -> str:
2982        this = self.sql(expression, key="this")
2983        return f"CHECK ({this})"
2984
2985    def foreignkey_sql(self, expression: exp.ForeignKey) -> str:
2986        expressions = self.expressions(expression, flat=True)
2987        expressions = f" ({expressions})" if expressions else ""
2988        reference = self.sql(expression, "reference")
2989        reference = f" {reference}" if reference else ""
2990        delete = self.sql(expression, "delete")
2991        delete = f" ON DELETE {delete}" if delete else ""
2992        update = self.sql(expression, "update")
2993        update = f" ON UPDATE {update}" if update else ""
2994        options = self.expressions(expression, key="options", flat=True, sep=" ")
2995        options = f" {options}" if options else ""
2996        return f"FOREIGN KEY{expressions}{reference}{delete}{update}{options}"
2997
2998    def primarykey_sql(self, expression: exp.ForeignKey) -> str:
2999        expressions = self.expressions(expression, flat=True)
3000        options = self.expressions(expression, key="options", flat=True, sep=" ")
3001        options = f" {options}" if options else ""
3002        return f"PRIMARY KEY ({expressions}){options}"
3003
3004    def if_sql(self, expression: exp.If) -> str:
3005        return self.case_sql(exp.Case(ifs=[expression], default=expression.args.get("false")))
3006
3007    def matchagainst_sql(self, expression: exp.MatchAgainst) -> str:
3008        modifier = expression.args.get("modifier")
3009        modifier = f" {modifier}" if modifier else ""
3010        return f"{self.func('MATCH', *expression.expressions)} AGAINST({self.sql(expression, 'this')}{modifier})"
3011
3012    def jsonkeyvalue_sql(self, expression: exp.JSONKeyValue) -> str:
3013        return f"{self.sql(expression, 'this')}{self.JSON_KEY_VALUE_PAIR_SEP} {self.sql(expression, 'expression')}"
3014
3015    def jsonpath_sql(self, expression: exp.JSONPath) -> str:
3016        path = self.expressions(expression, sep="", flat=True).lstrip(".")
3017
3018        if expression.args.get("escape"):
3019            path = self.escape_str(path)
3020
3021        if self.QUOTE_JSON_PATH:
3022            path = f"{self.dialect.QUOTE_START}{path}{self.dialect.QUOTE_END}"
3023
3024        return path
3025
3026    def json_path_part(self, expression: int | str | exp.JSONPathPart) -> str:
3027        if isinstance(expression, exp.JSONPathPart):
3028            transform = self.TRANSFORMS.get(expression.__class__)
3029            if not callable(transform):
3030                self.unsupported(f"Unsupported JSONPathPart type {expression.__class__.__name__}")
3031                return ""
3032
3033            return transform(self, expression)
3034
3035        if isinstance(expression, int):
3036            return str(expression)
3037
3038        if self._quote_json_path_key_using_brackets and self.JSON_PATH_SINGLE_QUOTE_ESCAPE:
3039            escaped = expression.replace("'", "\\'")
3040            escaped = f"\\'{expression}\\'"
3041        else:
3042            escaped = expression.replace('"', '\\"')
3043            escaped = f'"{escaped}"'
3044
3045        return escaped
3046
3047    def formatjson_sql(self, expression: exp.FormatJson) -> str:
3048        return f"{self.sql(expression, 'this')} FORMAT JSON"
3049
3050    def formatphrase_sql(self, expression: exp.FormatPhrase) -> str:
3051        # Output the Teradata column FORMAT override.
3052        # https://docs.teradata.com/r/Enterprise_IntelliFlex_VMware/SQL-Data-Types-and-Literals/Data-Type-Formats-and-Format-Phrases/FORMAT
3053        this = self.sql(expression, "this")
3054        fmt = self.sql(expression, "format")
3055        return f"{this} (FORMAT {fmt})"
3056
3057    def jsonobject_sql(self, expression: exp.JSONObject | exp.JSONObjectAgg) -> str:
3058        null_handling = expression.args.get("null_handling")
3059        null_handling = f" {null_handling}" if null_handling else ""
3060
3061        unique_keys = expression.args.get("unique_keys")
3062        if unique_keys is not None:
3063            unique_keys = f" {'WITH' if unique_keys else 'WITHOUT'} UNIQUE KEYS"
3064        else:
3065            unique_keys = ""
3066
3067        return_type = self.sql(expression, "return_type")
3068        return_type = f" RETURNING {return_type}" if return_type else ""
3069        encoding = self.sql(expression, "encoding")
3070        encoding = f" ENCODING {encoding}" if encoding else ""
3071
3072        return self.func(
3073            "JSON_OBJECT" if isinstance(expression, exp.JSONObject) else "JSON_OBJECTAGG",
3074            *expression.expressions,
3075            suffix=f"{null_handling}{unique_keys}{return_type}{encoding})",
3076        )
3077
3078    def jsonobjectagg_sql(self, expression: exp.JSONObjectAgg) -> str:
3079        return self.jsonobject_sql(expression)
3080
3081    def jsonarray_sql(self, expression: exp.JSONArray) -> str:
3082        null_handling = expression.args.get("null_handling")
3083        null_handling = f" {null_handling}" if null_handling else ""
3084        return_type = self.sql(expression, "return_type")
3085        return_type = f" RETURNING {return_type}" if return_type else ""
3086        strict = " STRICT" if expression.args.get("strict") else ""
3087        return self.func(
3088            "JSON_ARRAY", *expression.expressions, suffix=f"{null_handling}{return_type}{strict})"
3089        )
3090
3091    def jsonarrayagg_sql(self, expression: exp.JSONArrayAgg) -> str:
3092        this = self.sql(expression, "this")
3093        order = self.sql(expression, "order")
3094        null_handling = expression.args.get("null_handling")
3095        null_handling = f" {null_handling}" if null_handling else ""
3096        return_type = self.sql(expression, "return_type")
3097        return_type = f" RETURNING {return_type}" if return_type else ""
3098        strict = " STRICT" if expression.args.get("strict") else ""
3099        return self.func(
3100            "JSON_ARRAYAGG",
3101            this,
3102            suffix=f"{order}{null_handling}{return_type}{strict})",
3103        )
3104
3105    def jsoncolumndef_sql(self, expression: exp.JSONColumnDef) -> str:
3106        path = self.sql(expression, "path")
3107        path = f" PATH {path}" if path else ""
3108        nested_schema = self.sql(expression, "nested_schema")
3109
3110        if nested_schema:
3111            return f"NESTED{path} {nested_schema}"
3112
3113        this = self.sql(expression, "this")
3114        kind = self.sql(expression, "kind")
3115        kind = f" {kind}" if kind else ""
3116        return f"{this}{kind}{path}"
3117
3118    def jsonschema_sql(self, expression: exp.JSONSchema) -> str:
3119        return self.func("COLUMNS", *expression.expressions)
3120
3121    def jsontable_sql(self, expression: exp.JSONTable) -> str:
3122        this = self.sql(expression, "this")
3123        path = self.sql(expression, "path")
3124        path = f", {path}" if path else ""
3125        error_handling = expression.args.get("error_handling")
3126        error_handling = f" {error_handling}" if error_handling else ""
3127        empty_handling = expression.args.get("empty_handling")
3128        empty_handling = f" {empty_handling}" if empty_handling else ""
3129        schema = self.sql(expression, "schema")
3130        return self.func(
3131            "JSON_TABLE", this, suffix=f"{path}{error_handling}{empty_handling} {schema})"
3132        )
3133
3134    def openjsoncolumndef_sql(self, expression: exp.OpenJSONColumnDef) -> str:
3135        this = self.sql(expression, "this")
3136        kind = self.sql(expression, "kind")
3137        path = self.sql(expression, "path")
3138        path = f" {path}" if path else ""
3139        as_json = " AS JSON" if expression.args.get("as_json") else ""
3140        return f"{this} {kind}{path}{as_json}"
3141
3142    def openjson_sql(self, expression: exp.OpenJSON) -> str:
3143        this = self.sql(expression, "this")
3144        path = self.sql(expression, "path")
3145        path = f", {path}" if path else ""
3146        expressions = self.expressions(expression)
3147        with_ = (
3148            f" WITH ({self.seg(self.indent(expressions), sep='')}{self.seg(')', sep='')}"
3149            if expressions
3150            else ""
3151        )
3152        return f"OPENJSON({this}{path}){with_}"
3153
3154    def in_sql(self, expression: exp.In) -> str:
3155        query = expression.args.get("query")
3156        unnest = expression.args.get("unnest")
3157        field = expression.args.get("field")
3158        is_global = " GLOBAL" if expression.args.get("is_global") else ""
3159
3160        if query:
3161            in_sql = self.sql(query)
3162        elif unnest:
3163            in_sql = self.in_unnest_op(unnest)
3164        elif field:
3165            in_sql = self.sql(field)
3166        else:
3167            in_sql = f"({self.expressions(expression, dynamic=True, new_line=True, skip_first=True, skip_last=True)})"
3168
3169        return f"{self.sql(expression, 'this')}{is_global} IN {in_sql}"
3170
3171    def in_unnest_op(self, unnest: exp.Unnest) -> str:
3172        return f"(SELECT {self.sql(unnest)})"
3173
3174    def interval_sql(self, expression: exp.Interval) -> str:
3175        unit = self.sql(expression, "unit")
3176        if not self.INTERVAL_ALLOWS_PLURAL_FORM:
3177            unit = self.TIME_PART_SINGULARS.get(unit, unit)
3178        unit = f" {unit}" if unit else ""
3179
3180        if self.SINGLE_STRING_INTERVAL:
3181            this = expression.this.name if expression.this else ""
3182            return f"INTERVAL '{this}{unit}'" if this else f"INTERVAL{unit}"
3183
3184        this = self.sql(expression, "this")
3185        if this:
3186            unwrapped = isinstance(expression.this, self.UNWRAPPED_INTERVAL_VALUES)
3187            this = f" {this}" if unwrapped else f" ({this})"
3188
3189        return f"INTERVAL{this}{unit}"
3190
3191    def return_sql(self, expression: exp.Return) -> str:
3192        return f"RETURN {self.sql(expression, 'this')}"
3193
3194    def reference_sql(self, expression: exp.Reference) -> str:
3195        this = self.sql(expression, "this")
3196        expressions = self.expressions(expression, flat=True)
3197        expressions = f"({expressions})" if expressions else ""
3198        options = self.expressions(expression, key="options", flat=True, sep=" ")
3199        options = f" {options}" if options else ""
3200        return f"REFERENCES {this}{expressions}{options}"
3201
3202    def anonymous_sql(self, expression: exp.Anonymous) -> str:
3203        # We don't normalize qualified functions such as a.b.foo(), because they can be case-sensitive
3204        parent = expression.parent
3205        is_qualified = isinstance(parent, exp.Dot) and expression is parent.expression
3206        return self.func(
3207            self.sql(expression, "this"), *expression.expressions, normalize=not is_qualified
3208        )
3209
3210    def paren_sql(self, expression: exp.Paren) -> str:
3211        sql = self.seg(self.indent(self.sql(expression, "this")), sep="")
3212        return f"({sql}{self.seg(')', sep='')}"
3213
3214    def neg_sql(self, expression: exp.Neg) -> str:
3215        # This makes sure we don't convert "- - 5" to "--5", which is a comment
3216        this_sql = self.sql(expression, "this")
3217        sep = " " if this_sql[0] == "-" else ""
3218        return f"-{sep}{this_sql}"
3219
3220    def not_sql(self, expression: exp.Not) -> str:
3221        return f"NOT {self.sql(expression, 'this')}"
3222
3223    def alias_sql(self, expression: exp.Alias) -> str:
3224        alias = self.sql(expression, "alias")
3225        alias = f" AS {alias}" if alias else ""
3226        return f"{self.sql(expression, 'this')}{alias}"
3227
3228    def pivotalias_sql(self, expression: exp.PivotAlias) -> str:
3229        alias = expression.args["alias"]
3230
3231        parent = expression.parent
3232        pivot = parent and parent.parent
3233
3234        if isinstance(pivot, exp.Pivot) and pivot.unpivot:
3235            identifier_alias = isinstance(alias, exp.Identifier)
3236            literal_alias = isinstance(alias, exp.Literal)
3237
3238            if identifier_alias and not self.UNPIVOT_ALIASES_ARE_IDENTIFIERS:
3239                alias.replace(exp.Literal.string(alias.output_name))
3240            elif not identifier_alias and literal_alias and self.UNPIVOT_ALIASES_ARE_IDENTIFIERS:
3241                alias.replace(exp.to_identifier(alias.output_name))
3242
3243        return self.alias_sql(expression)
3244
3245    def aliases_sql(self, expression: exp.Aliases) -> str:
3246        return f"{self.sql(expression, 'this')} AS ({self.expressions(expression, flat=True)})"
3247
3248    def atindex_sql(self, expression: exp.AtTimeZone) -> str:
3249        this = self.sql(expression, "this")
3250        index = self.sql(expression, "expression")
3251        return f"{this} AT {index}"
3252
3253    def attimezone_sql(self, expression: exp.AtTimeZone) -> str:
3254        this = self.sql(expression, "this")
3255        zone = self.sql(expression, "zone")
3256        return f"{this} AT TIME ZONE {zone}"
3257
3258    def fromtimezone_sql(self, expression: exp.FromTimeZone) -> str:
3259        this = self.sql(expression, "this")
3260        zone = self.sql(expression, "zone")
3261        return f"{this} AT TIME ZONE {zone} AT TIME ZONE 'UTC'"
3262
3263    def add_sql(self, expression: exp.Add) -> str:
3264        return self.binary(expression, "+")
3265
3266    def and_sql(
3267        self, expression: exp.And, stack: t.Optional[t.List[str | exp.Expression]] = None
3268    ) -> str:
3269        return self.connector_sql(expression, "AND", stack)
3270
3271    def or_sql(
3272        self, expression: exp.Or, stack: t.Optional[t.List[str | exp.Expression]] = None
3273    ) -> str:
3274        return self.connector_sql(expression, "OR", stack)
3275
3276    def xor_sql(
3277        self, expression: exp.Xor, stack: t.Optional[t.List[str | exp.Expression]] = None
3278    ) -> str:
3279        return self.connector_sql(expression, "XOR", stack)
3280
3281    def connector_sql(
3282        self,
3283        expression: exp.Connector,
3284        op: str,
3285        stack: t.Optional[t.List[str | exp.Expression]] = None,
3286    ) -> str:
3287        if stack is not None:
3288            if expression.expressions:
3289                stack.append(self.expressions(expression, sep=f" {op} "))
3290            else:
3291                stack.append(expression.right)
3292                if expression.comments and self.comments:
3293                    for comment in expression.comments:
3294                        if comment:
3295                            op += f" /*{self.sanitize_comment(comment)}*/"
3296                stack.extend((op, expression.left))
3297            return op
3298
3299        stack = [expression]
3300        sqls: t.List[str] = []
3301        ops = set()
3302
3303        while stack:
3304            node = stack.pop()
3305            if isinstance(node, exp.Connector):
3306                ops.add(getattr(self, f"{node.key}_sql")(node, stack))
3307            else:
3308                sql = self.sql(node)
3309                if sqls and sqls[-1] in ops:
3310                    sqls[-1] += f" {sql}"
3311                else:
3312                    sqls.append(sql)
3313
3314        sep = "\n" if self.pretty and self.too_wide(sqls) else " "
3315        return sep.join(sqls)
3316
3317    def bitwiseand_sql(self, expression: exp.BitwiseAnd) -> str:
3318        return self.binary(expression, "&")
3319
3320    def bitwiseleftshift_sql(self, expression: exp.BitwiseLeftShift) -> str:
3321        return self.binary(expression, "<<")
3322
3323    def bitwisenot_sql(self, expression: exp.BitwiseNot) -> str:
3324        return f"~{self.sql(expression, 'this')}"
3325
3326    def bitwiseor_sql(self, expression: exp.BitwiseOr) -> str:
3327        return self.binary(expression, "|")
3328
3329    def bitwiserightshift_sql(self, expression: exp.BitwiseRightShift) -> str:
3330        return self.binary(expression, ">>")
3331
3332    def bitwisexor_sql(self, expression: exp.BitwiseXor) -> str:
3333        return self.binary(expression, "^")
3334
3335    def cast_sql(self, expression: exp.Cast, safe_prefix: t.Optional[str] = None) -> str:
3336        format_sql = self.sql(expression, "format")
3337        format_sql = f" FORMAT {format_sql}" if format_sql else ""
3338        to_sql = self.sql(expression, "to")
3339        to_sql = f" {to_sql}" if to_sql else ""
3340        action = self.sql(expression, "action")
3341        action = f" {action}" if action else ""
3342        default = self.sql(expression, "default")
3343        default = f" DEFAULT {default} ON CONVERSION ERROR" if default else ""
3344        return f"{safe_prefix or ''}CAST({self.sql(expression, 'this')} AS{to_sql}{default}{format_sql}{action})"
3345
3346    def currentdate_sql(self, expression: exp.CurrentDate) -> str:
3347        zone = self.sql(expression, "this")
3348        return f"CURRENT_DATE({zone})" if zone else "CURRENT_DATE"
3349
3350    def collate_sql(self, expression: exp.Collate) -> str:
3351        if self.COLLATE_IS_FUNC:
3352            return self.function_fallback_sql(expression)
3353        return self.binary(expression, "COLLATE")
3354
3355    def command_sql(self, expression: exp.Command) -> str:
3356        return f"{self.sql(expression, 'this')} {expression.text('expression').strip()}"
3357
3358    def comment_sql(self, expression: exp.Comment) -> str:
3359        this = self.sql(expression, "this")
3360        kind = expression.args["kind"]
3361        materialized = " MATERIALIZED" if expression.args.get("materialized") else ""
3362        exists_sql = " IF EXISTS " if expression.args.get("exists") else " "
3363        expression_sql = self.sql(expression, "expression")
3364        return f"COMMENT{exists_sql}ON{materialized} {kind} {this} IS {expression_sql}"
3365
3366    def mergetreettlaction_sql(self, expression: exp.MergeTreeTTLAction) -> str:
3367        this = self.sql(expression, "this")
3368        delete = " DELETE" if expression.args.get("delete") else ""
3369        recompress = self.sql(expression, "recompress")
3370        recompress = f" RECOMPRESS {recompress}" if recompress else ""
3371        to_disk = self.sql(expression, "to_disk")
3372        to_disk = f" TO DISK {to_disk}" if to_disk else ""
3373        to_volume = self.sql(expression, "to_volume")
3374        to_volume = f" TO VOLUME {to_volume}" if to_volume else ""
3375        return f"{this}{delete}{recompress}{to_disk}{to_volume}"
3376
3377    def mergetreettl_sql(self, expression: exp.MergeTreeTTL) -> str:
3378        where = self.sql(expression, "where")
3379        group = self.sql(expression, "group")
3380        aggregates = self.expressions(expression, key="aggregates")
3381        aggregates = self.seg("SET") + self.seg(aggregates) if aggregates else ""
3382
3383        if not (where or group or aggregates) and len(expression.expressions) == 1:
3384            return f"TTL {self.expressions(expression, flat=True)}"
3385
3386        return f"TTL{self.seg(self.expressions(expression))}{where}{group}{aggregates}"
3387
3388    def transaction_sql(self, expression: exp.Transaction) -> str:
3389        return "BEGIN"
3390
3391    def commit_sql(self, expression: exp.Commit) -> str:
3392        chain = expression.args.get("chain")
3393        if chain is not None:
3394            chain = " AND CHAIN" if chain else " AND NO CHAIN"
3395
3396        return f"COMMIT{chain or ''}"
3397
3398    def rollback_sql(self, expression: exp.Rollback) -> str:
3399        savepoint = expression.args.get("savepoint")
3400        savepoint = f" TO {savepoint}" if savepoint else ""
3401        return f"ROLLBACK{savepoint}"
3402
3403    def altercolumn_sql(self, expression: exp.AlterColumn) -> str:
3404        this = self.sql(expression, "this")
3405
3406        dtype = self.sql(expression, "dtype")
3407        if dtype:
3408            collate = self.sql(expression, "collate")
3409            collate = f" COLLATE {collate}" if collate else ""
3410            using = self.sql(expression, "using")
3411            using = f" USING {using}" if using else ""
3412            alter_set_type = self.ALTER_SET_TYPE + " " if self.ALTER_SET_TYPE else ""
3413            return f"ALTER COLUMN {this} {alter_set_type}{dtype}{collate}{using}"
3414
3415        default = self.sql(expression, "default")
3416        if default:
3417            return f"ALTER COLUMN {this} SET DEFAULT {default}"
3418
3419        comment = self.sql(expression, "comment")
3420        if comment:
3421            return f"ALTER COLUMN {this} COMMENT {comment}"
3422
3423        visible = expression.args.get("visible")
3424        if visible:
3425            return f"ALTER COLUMN {this} SET {visible}"
3426
3427        allow_null = expression.args.get("allow_null")
3428        drop = expression.args.get("drop")
3429
3430        if not drop and not allow_null:
3431            self.unsupported("Unsupported ALTER COLUMN syntax")
3432
3433        if allow_null is not None:
3434            keyword = "DROP" if drop else "SET"
3435            return f"ALTER COLUMN {this} {keyword} NOT NULL"
3436
3437        return f"ALTER COLUMN {this} DROP DEFAULT"
3438
3439    def alterindex_sql(self, expression: exp.AlterIndex) -> str:
3440        this = self.sql(expression, "this")
3441
3442        visible = expression.args.get("visible")
3443        visible_sql = "VISIBLE" if visible else "INVISIBLE"
3444
3445        return f"ALTER INDEX {this} {visible_sql}"
3446
3447    def alterdiststyle_sql(self, expression: exp.AlterDistStyle) -> str:
3448        this = self.sql(expression, "this")
3449        if not isinstance(expression.this, exp.Var):
3450            this = f"KEY DISTKEY {this}"
3451        return f"ALTER DISTSTYLE {this}"
3452
3453    def altersortkey_sql(self, expression: exp.AlterSortKey) -> str:
3454        compound = " COMPOUND" if expression.args.get("compound") else ""
3455        this = self.sql(expression, "this")
3456        expressions = self.expressions(expression, flat=True)
3457        expressions = f"({expressions})" if expressions else ""
3458        return f"ALTER{compound} SORTKEY {this or expressions}"
3459
3460    def alterrename_sql(self, expression: exp.AlterRename) -> str:
3461        if not self.RENAME_TABLE_WITH_DB:
3462            # Remove db from tables
3463            expression = expression.transform(
3464                lambda n: exp.table_(n.this) if isinstance(n, exp.Table) else n
3465            ).assert_is(exp.AlterRename)
3466        this = self.sql(expression, "this")
3467        return f"RENAME TO {this}"
3468
3469    def renamecolumn_sql(self, expression: exp.RenameColumn) -> str:
3470        exists = " IF EXISTS" if expression.args.get("exists") else ""
3471        old_column = self.sql(expression, "this")
3472        new_column = self.sql(expression, "to")
3473        return f"RENAME COLUMN{exists} {old_column} TO {new_column}"
3474
3475    def alterset_sql(self, expression: exp.AlterSet) -> str:
3476        exprs = self.expressions(expression, flat=True)
3477        if self.ALTER_SET_WRAPPED:
3478            exprs = f"({exprs})"
3479
3480        return f"SET {exprs}"
3481
3482    def alter_sql(self, expression: exp.Alter) -> str:
3483        actions = expression.args["actions"]
3484
3485        if not self.dialect.ALTER_TABLE_ADD_REQUIRED_FOR_EACH_COLUMN and isinstance(
3486            actions[0], exp.ColumnDef
3487        ):
3488            actions_sql = self.expressions(expression, key="actions", flat=True)
3489            actions_sql = f"ADD {actions_sql}"
3490        else:
3491            actions_list = []
3492            for action in actions:
3493                if isinstance(action, (exp.ColumnDef, exp.Schema)):
3494                    action_sql = self.add_column_sql(action)
3495                else:
3496                    action_sql = self.sql(action)
3497                    if isinstance(action, exp.Query):
3498                        action_sql = f"AS {action_sql}"
3499
3500                actions_list.append(action_sql)
3501
3502            actions_sql = self.format_args(*actions_list).lstrip("\n")
3503
3504        exists = " IF EXISTS" if expression.args.get("exists") else ""
3505        on_cluster = self.sql(expression, "cluster")
3506        on_cluster = f" {on_cluster}" if on_cluster else ""
3507        only = " ONLY" if expression.args.get("only") else ""
3508        options = self.expressions(expression, key="options")
3509        options = f", {options}" if options else ""
3510        kind = self.sql(expression, "kind")
3511        not_valid = " NOT VALID" if expression.args.get("not_valid") else ""
3512
3513        return f"ALTER {kind}{exists}{only} {self.sql(expression, 'this')}{on_cluster}{self.sep()}{actions_sql}{not_valid}{options}"
3514
3515    def add_column_sql(self, expression: exp.Expression) -> str:
3516        sql = self.sql(expression)
3517        if isinstance(expression, exp.Schema):
3518            column_text = " COLUMNS"
3519        elif isinstance(expression, exp.ColumnDef) and self.ALTER_TABLE_INCLUDE_COLUMN_KEYWORD:
3520            column_text = " COLUMN"
3521        else:
3522            column_text = ""
3523
3524        return f"ADD{column_text} {sql}"
3525
3526    def droppartition_sql(self, expression: exp.DropPartition) -> str:
3527        expressions = self.expressions(expression)
3528        exists = " IF EXISTS " if expression.args.get("exists") else " "
3529        return f"DROP{exists}{expressions}"
3530
3531    def addconstraint_sql(self, expression: exp.AddConstraint) -> str:
3532        return f"ADD {self.expressions(expression, indent=False)}"
3533
3534    def addpartition_sql(self, expression: exp.AddPartition) -> str:
3535        exists = "IF NOT EXISTS " if expression.args.get("exists") else ""
3536        return f"ADD {exists}{self.sql(expression.this)}"
3537
3538    def distinct_sql(self, expression: exp.Distinct) -> str:
3539        this = self.expressions(expression, flat=True)
3540
3541        if not self.MULTI_ARG_DISTINCT and len(expression.expressions) > 1:
3542            case = exp.case()
3543            for arg in expression.expressions:
3544                case = case.when(arg.is_(exp.null()), exp.null())
3545            this = self.sql(case.else_(f"({this})"))
3546
3547        this = f" {this}" if this else ""
3548
3549        on = self.sql(expression, "on")
3550        on = f" ON {on}" if on else ""
3551        return f"DISTINCT{this}{on}"
3552
3553    def ignorenulls_sql(self, expression: exp.IgnoreNulls) -> str:
3554        return self._embed_ignore_nulls(expression, "IGNORE NULLS")
3555
3556    def respectnulls_sql(self, expression: exp.RespectNulls) -> str:
3557        return self._embed_ignore_nulls(expression, "RESPECT NULLS")
3558
3559    def havingmax_sql(self, expression: exp.HavingMax) -> str:
3560        this_sql = self.sql(expression, "this")
3561        expression_sql = self.sql(expression, "expression")
3562        kind = "MAX" if expression.args.get("max") else "MIN"
3563        return f"{this_sql} HAVING {kind} {expression_sql}"
3564
3565    def intdiv_sql(self, expression: exp.IntDiv) -> str:
3566        return self.sql(
3567            exp.Cast(
3568                this=exp.Div(this=expression.this, expression=expression.expression),
3569                to=exp.DataType(this=exp.DataType.Type.INT),
3570            )
3571        )
3572
3573    def dpipe_sql(self, expression: exp.DPipe) -> str:
3574        if self.dialect.STRICT_STRING_CONCAT and expression.args.get("safe"):
3575            return self.func(
3576                "CONCAT", *(exp.cast(e, exp.DataType.Type.TEXT) for e in expression.flatten())
3577            )
3578        return self.binary(expression, "||")
3579
3580    def div_sql(self, expression: exp.Div) -> str:
3581        l, r = expression.left, expression.right
3582
3583        if not self.dialect.SAFE_DIVISION and expression.args.get("safe"):
3584            r.replace(exp.Nullif(this=r.copy(), expression=exp.Literal.number(0)))
3585
3586        if self.dialect.TYPED_DIVISION and not expression.args.get("typed"):
3587            if not l.is_type(*exp.DataType.REAL_TYPES) and not r.is_type(*exp.DataType.REAL_TYPES):
3588                l.replace(exp.cast(l.copy(), to=exp.DataType.Type.DOUBLE))
3589
3590        elif not self.dialect.TYPED_DIVISION and expression.args.get("typed"):
3591            if l.is_type(*exp.DataType.INTEGER_TYPES) and r.is_type(*exp.DataType.INTEGER_TYPES):
3592                return self.sql(
3593                    exp.cast(
3594                        l / r,
3595                        to=exp.DataType.Type.BIGINT,
3596                    )
3597                )
3598
3599        return self.binary(expression, "/")
3600
3601    def safedivide_sql(self, expression: exp.SafeDivide) -> str:
3602        n = exp._wrap(expression.this, exp.Binary)
3603        d = exp._wrap(expression.expression, exp.Binary)
3604        return self.sql(exp.If(this=d.neq(0), true=n / d, false=exp.Null()))
3605
3606    def overlaps_sql(self, expression: exp.Overlaps) -> str:
3607        return self.binary(expression, "OVERLAPS")
3608
3609    def distance_sql(self, expression: exp.Distance) -> str:
3610        return self.binary(expression, "<->")
3611
3612    def dot_sql(self, expression: exp.Dot) -> str:
3613        return f"{self.sql(expression, 'this')}.{self.sql(expression, 'expression')}"
3614
3615    def eq_sql(self, expression: exp.EQ) -> str:
3616        return self.binary(expression, "=")
3617
3618    def propertyeq_sql(self, expression: exp.PropertyEQ) -> str:
3619        return self.binary(expression, ":=")
3620
3621    def escape_sql(self, expression: exp.Escape) -> str:
3622        return self.binary(expression, "ESCAPE")
3623
3624    def glob_sql(self, expression: exp.Glob) -> str:
3625        return self.binary(expression, "GLOB")
3626
3627    def gt_sql(self, expression: exp.GT) -> str:
3628        return self.binary(expression, ">")
3629
3630    def gte_sql(self, expression: exp.GTE) -> str:
3631        return self.binary(expression, ">=")
3632
3633    def ilike_sql(self, expression: exp.ILike) -> str:
3634        return self.binary(expression, "ILIKE")
3635
3636    def ilikeany_sql(self, expression: exp.ILikeAny) -> str:
3637        return self.binary(expression, "ILIKE ANY")
3638
3639    def is_sql(self, expression: exp.Is) -> str:
3640        if not self.IS_BOOL_ALLOWED and isinstance(expression.expression, exp.Boolean):
3641            return self.sql(
3642                expression.this if expression.expression.this else exp.not_(expression.this)
3643            )
3644        return self.binary(expression, "IS")
3645
3646    def like_sql(self, expression: exp.Like) -> str:
3647        return self.binary(expression, "LIKE")
3648
3649    def likeany_sql(self, expression: exp.LikeAny) -> str:
3650        return self.binary(expression, "LIKE ANY")
3651
3652    def similarto_sql(self, expression: exp.SimilarTo) -> str:
3653        return self.binary(expression, "SIMILAR TO")
3654
3655    def lt_sql(self, expression: exp.LT) -> str:
3656        return self.binary(expression, "<")
3657
3658    def lte_sql(self, expression: exp.LTE) -> str:
3659        return self.binary(expression, "<=")
3660
3661    def mod_sql(self, expression: exp.Mod) -> str:
3662        return self.binary(expression, "%")
3663
3664    def mul_sql(self, expression: exp.Mul) -> str:
3665        return self.binary(expression, "*")
3666
3667    def neq_sql(self, expression: exp.NEQ) -> str:
3668        return self.binary(expression, "<>")
3669
3670    def nullsafeeq_sql(self, expression: exp.NullSafeEQ) -> str:
3671        return self.binary(expression, "IS NOT DISTINCT FROM")
3672
3673    def nullsafeneq_sql(self, expression: exp.NullSafeNEQ) -> str:
3674        return self.binary(expression, "IS DISTINCT FROM")
3675
3676    def slice_sql(self, expression: exp.Slice) -> str:
3677        return self.binary(expression, ":")
3678
3679    def sub_sql(self, expression: exp.Sub) -> str:
3680        return self.binary(expression, "-")
3681
3682    def trycast_sql(self, expression: exp.TryCast) -> str:
3683        return self.cast_sql(expression, safe_prefix="TRY_")
3684
3685    def jsoncast_sql(self, expression: exp.JSONCast) -> str:
3686        return self.cast_sql(expression)
3687
3688    def try_sql(self, expression: exp.Try) -> str:
3689        if not self.TRY_SUPPORTED:
3690            self.unsupported("Unsupported TRY function")
3691            return self.sql(expression, "this")
3692
3693        return self.func("TRY", expression.this)
3694
3695    def log_sql(self, expression: exp.Log) -> str:
3696        this = expression.this
3697        expr = expression.expression
3698
3699        if self.dialect.LOG_BASE_FIRST is False:
3700            this, expr = expr, this
3701        elif self.dialect.LOG_BASE_FIRST is None and expr:
3702            if this.name in ("2", "10"):
3703                return self.func(f"LOG{this.name}", expr)
3704
3705            self.unsupported(f"Unsupported logarithm with base {self.sql(this)}")
3706
3707        return self.func("LOG", this, expr)
3708
3709    def use_sql(self, expression: exp.Use) -> str:
3710        kind = self.sql(expression, "kind")
3711        kind = f" {kind}" if kind else ""
3712        this = self.sql(expression, "this") or self.expressions(expression, flat=True)
3713        this = f" {this}" if this else ""
3714        return f"USE{kind}{this}"
3715
3716    def binary(self, expression: exp.Binary, op: str) -> str:
3717        sqls: t.List[str] = []
3718        stack: t.List[t.Union[str, exp.Expression]] = [expression]
3719        binary_type = type(expression)
3720
3721        while stack:
3722            node = stack.pop()
3723
3724            if type(node) is binary_type:
3725                op_func = node.args.get("operator")
3726                if op_func:
3727                    op = f"OPERATOR({self.sql(op_func)})"
3728
3729                stack.append(node.right)
3730                stack.append(f" {self.maybe_comment(op, comments=node.comments)} ")
3731                stack.append(node.left)
3732            else:
3733                sqls.append(self.sql(node))
3734
3735        return "".join(sqls)
3736
3737    def ceil_floor(self, expression: exp.Ceil | exp.Floor) -> str:
3738        to_clause = self.sql(expression, "to")
3739        if to_clause:
3740            return f"{expression.sql_name()}({self.sql(expression, 'this')} TO {to_clause})"
3741
3742        return self.function_fallback_sql(expression)
3743
3744    def function_fallback_sql(self, expression: exp.Func) -> str:
3745        args = []
3746
3747        for key in expression.arg_types:
3748            arg_value = expression.args.get(key)
3749
3750            if isinstance(arg_value, list):
3751                for value in arg_value:
3752                    args.append(value)
3753            elif arg_value is not None:
3754                args.append(arg_value)
3755
3756        if self.dialect.PRESERVE_ORIGINAL_NAMES:
3757            name = (expression._meta and expression.meta.get("name")) or expression.sql_name()
3758        else:
3759            name = expression.sql_name()
3760
3761        return self.func(name, *args)
3762
3763    def func(
3764        self,
3765        name: str,
3766        *args: t.Optional[exp.Expression | str],
3767        prefix: str = "(",
3768        suffix: str = ")",
3769        normalize: bool = True,
3770    ) -> str:
3771        name = self.normalize_func(name) if normalize else name
3772        return f"{name}{prefix}{self.format_args(*args)}{suffix}"
3773
3774    def format_args(self, *args: t.Optional[str | exp.Expression], sep: str = ", ") -> str:
3775        arg_sqls = tuple(
3776            self.sql(arg) for arg in args if arg is not None and not isinstance(arg, bool)
3777        )
3778        if self.pretty and self.too_wide(arg_sqls):
3779            return self.indent(
3780                "\n" + f"{sep.strip()}\n".join(arg_sqls) + "\n", skip_first=True, skip_last=True
3781            )
3782        return sep.join(arg_sqls)
3783
3784    def too_wide(self, args: t.Iterable) -> bool:
3785        return sum(len(arg) for arg in args) > self.max_text_width
3786
3787    def format_time(
3788        self,
3789        expression: exp.Expression,
3790        inverse_time_mapping: t.Optional[t.Dict[str, str]] = None,
3791        inverse_time_trie: t.Optional[t.Dict] = None,
3792    ) -> t.Optional[str]:
3793        return format_time(
3794            self.sql(expression, "format"),
3795            inverse_time_mapping or self.dialect.INVERSE_TIME_MAPPING,
3796            inverse_time_trie or self.dialect.INVERSE_TIME_TRIE,
3797        )
3798
3799    def expressions(
3800        self,
3801        expression: t.Optional[exp.Expression] = None,
3802        key: t.Optional[str] = None,
3803        sqls: t.Optional[t.Collection[str | exp.Expression]] = None,
3804        flat: bool = False,
3805        indent: bool = True,
3806        skip_first: bool = False,
3807        skip_last: bool = False,
3808        sep: str = ", ",
3809        prefix: str = "",
3810        dynamic: bool = False,
3811        new_line: bool = False,
3812    ) -> str:
3813        expressions = expression.args.get(key or "expressions") if expression else sqls
3814
3815        if not expressions:
3816            return ""
3817
3818        if flat:
3819            return sep.join(sql for sql in (self.sql(e) for e in expressions) if sql)
3820
3821        num_sqls = len(expressions)
3822        result_sqls = []
3823
3824        for i, e in enumerate(expressions):
3825            sql = self.sql(e, comment=False)
3826            if not sql:
3827                continue
3828
3829            comments = self.maybe_comment("", e) if isinstance(e, exp.Expression) else ""
3830
3831            if self.pretty:
3832                if self.leading_comma:
3833                    result_sqls.append(f"{sep if i > 0 else ''}{prefix}{sql}{comments}")
3834                else:
3835                    result_sqls.append(
3836                        f"{prefix}{sql}{(sep.rstrip() if comments else sep) if i + 1 < num_sqls else ''}{comments}"
3837                    )
3838            else:
3839                result_sqls.append(f"{prefix}{sql}{comments}{sep if i + 1 < num_sqls else ''}")
3840
3841        if self.pretty and (not dynamic or self.too_wide(result_sqls)):
3842            if new_line:
3843                result_sqls.insert(0, "")
3844                result_sqls.append("")
3845            result_sql = "\n".join(s.rstrip() for s in result_sqls)
3846        else:
3847            result_sql = "".join(result_sqls)
3848
3849        return (
3850            self.indent(result_sql, skip_first=skip_first, skip_last=skip_last)
3851            if indent
3852            else result_sql
3853        )
3854
3855    def op_expressions(self, op: str, expression: exp.Expression, flat: bool = False) -> str:
3856        flat = flat or isinstance(expression.parent, exp.Properties)
3857        expressions_sql = self.expressions(expression, flat=flat)
3858        if flat:
3859            return f"{op} {expressions_sql}"
3860        return f"{self.seg(op)}{self.sep() if expressions_sql else ''}{expressions_sql}"
3861
3862    def naked_property(self, expression: exp.Property) -> str:
3863        property_name = exp.Properties.PROPERTY_TO_NAME.get(expression.__class__)
3864        if not property_name:
3865            self.unsupported(f"Unsupported property {expression.__class__.__name__}")
3866        return f"{property_name} {self.sql(expression, 'this')}"
3867
3868    def tag_sql(self, expression: exp.Tag) -> str:
3869        return f"{expression.args.get('prefix')}{self.sql(expression.this)}{expression.args.get('postfix')}"
3870
3871    def token_sql(self, token_type: TokenType) -> str:
3872        return self.TOKEN_MAPPING.get(token_type, token_type.name)
3873
3874    def userdefinedfunction_sql(self, expression: exp.UserDefinedFunction) -> str:
3875        this = self.sql(expression, "this")
3876        expressions = self.no_identify(self.expressions, expression)
3877        expressions = (
3878            self.wrap(expressions) if expression.args.get("wrapped") else f" {expressions}"
3879        )
3880        return f"{this}{expressions}" if expressions.strip() != "" else this
3881
3882    def joinhint_sql(self, expression: exp.JoinHint) -> str:
3883        this = self.sql(expression, "this")
3884        expressions = self.expressions(expression, flat=True)
3885        return f"{this}({expressions})"
3886
3887    def kwarg_sql(self, expression: exp.Kwarg) -> str:
3888        return self.binary(expression, "=>")
3889
3890    def when_sql(self, expression: exp.When) -> str:
3891        matched = "MATCHED" if expression.args["matched"] else "NOT MATCHED"
3892        source = " BY SOURCE" if self.MATCHED_BY_SOURCE and expression.args.get("source") else ""
3893        condition = self.sql(expression, "condition")
3894        condition = f" AND {condition}" if condition else ""
3895
3896        then_expression = expression.args.get("then")
3897        if isinstance(then_expression, exp.Insert):
3898            this = self.sql(then_expression, "this")
3899            this = f"INSERT {this}" if this else "INSERT"
3900            then = self.sql(then_expression, "expression")
3901            then = f"{this} VALUES {then}" if then else this
3902        elif isinstance(then_expression, exp.Update):
3903            if isinstance(then_expression.args.get("expressions"), exp.Star):
3904                then = f"UPDATE {self.sql(then_expression, 'expressions')}"
3905            else:
3906                then = f"UPDATE SET{self.sep()}{self.expressions(then_expression)}"
3907        else:
3908            then = self.sql(then_expression)
3909        return f"WHEN {matched}{source}{condition} THEN {then}"
3910
3911    def whens_sql(self, expression: exp.Whens) -> str:
3912        return self.expressions(expression, sep=" ", indent=False)
3913
3914    def merge_sql(self, expression: exp.Merge) -> str:
3915        table = expression.this
3916        table_alias = ""
3917
3918        hints = table.args.get("hints")
3919        if hints and table.alias and isinstance(hints[0], exp.WithTableHint):
3920            # T-SQL syntax is MERGE ... <target_table> [WITH (<merge_hint>)] [[AS] table_alias]
3921            table_alias = f" AS {self.sql(table.args['alias'].pop())}"
3922
3923        this = self.sql(table)
3924        using = f"USING {self.sql(expression, 'using')}"
3925        on = f"ON {self.sql(expression, 'on')}"
3926        whens = self.sql(expression, "whens")
3927
3928        returning = self.sql(expression, "returning")
3929        if returning:
3930            whens = f"{whens}{returning}"
3931
3932        sep = self.sep()
3933
3934        return self.prepend_ctes(
3935            expression,
3936            f"MERGE INTO {this}{table_alias}{sep}{using}{sep}{on}{sep}{whens}",
3937        )
3938
3939    @unsupported_args("format")
3940    def tochar_sql(self, expression: exp.ToChar) -> str:
3941        return self.sql(exp.cast(expression.this, exp.DataType.Type.TEXT))
3942
3943    def tonumber_sql(self, expression: exp.ToNumber) -> str:
3944        if not self.SUPPORTS_TO_NUMBER:
3945            self.unsupported("Unsupported TO_NUMBER function")
3946            return self.sql(exp.cast(expression.this, exp.DataType.Type.DOUBLE))
3947
3948        fmt = expression.args.get("format")
3949        if not fmt:
3950            self.unsupported("Conversion format is required for TO_NUMBER")
3951            return self.sql(exp.cast(expression.this, exp.DataType.Type.DOUBLE))
3952
3953        return self.func("TO_NUMBER", expression.this, fmt)
3954
3955    def dictproperty_sql(self, expression: exp.DictProperty) -> str:
3956        this = self.sql(expression, "this")
3957        kind = self.sql(expression, "kind")
3958        settings_sql = self.expressions(expression, key="settings", sep=" ")
3959        args = f"({self.sep('')}{settings_sql}{self.seg(')', sep='')}" if settings_sql else "()"
3960        return f"{this}({kind}{args})"
3961
3962    def dictrange_sql(self, expression: exp.DictRange) -> str:
3963        this = self.sql(expression, "this")
3964        max = self.sql(expression, "max")
3965        min = self.sql(expression, "min")
3966        return f"{this}(MIN {min} MAX {max})"
3967
3968    def dictsubproperty_sql(self, expression: exp.DictSubProperty) -> str:
3969        return f"{self.sql(expression, 'this')} {self.sql(expression, 'value')}"
3970
3971    def duplicatekeyproperty_sql(self, expression: exp.DuplicateKeyProperty) -> str:
3972        return f"DUPLICATE KEY ({self.expressions(expression, flat=True)})"
3973
3974    # https://docs.starrocks.io/docs/sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE/
3975    def uniquekeyproperty_sql(self, expression: exp.UniqueKeyProperty) -> str:
3976        return f"UNIQUE KEY ({self.expressions(expression, flat=True)})"
3977
3978    # https://docs.starrocks.io/docs/sql-reference/sql-statements/data-definition/CREATE_TABLE/#distribution_desc
3979    def distributedbyproperty_sql(self, expression: exp.DistributedByProperty) -> str:
3980        expressions = self.expressions(expression, flat=True)
3981        expressions = f" {self.wrap(expressions)}" if expressions else ""
3982        buckets = self.sql(expression, "buckets")
3983        kind = self.sql(expression, "kind")
3984        buckets = f" BUCKETS {buckets}" if buckets else ""
3985        order = self.sql(expression, "order")
3986        return f"DISTRIBUTED BY {kind}{expressions}{buckets}{order}"
3987
3988    def oncluster_sql(self, expression: exp.OnCluster) -> str:
3989        return ""
3990
3991    def clusteredbyproperty_sql(self, expression: exp.ClusteredByProperty) -> str:
3992        expressions = self.expressions(expression, key="expressions", flat=True)
3993        sorted_by = self.expressions(expression, key="sorted_by", flat=True)
3994        sorted_by = f" SORTED BY ({sorted_by})" if sorted_by else ""
3995        buckets = self.sql(expression, "buckets")
3996        return f"CLUSTERED BY ({expressions}){sorted_by} INTO {buckets} BUCKETS"
3997
3998    def anyvalue_sql(self, expression: exp.AnyValue) -> str:
3999        this = self.sql(expression, "this")
4000        having = self.sql(expression, "having")
4001
4002        if having:
4003            this = f"{this} HAVING {'MAX' if expression.args.get('max') else 'MIN'} {having}"
4004
4005        return self.func("ANY_VALUE", this)
4006
4007    def querytransform_sql(self, expression: exp.QueryTransform) -> str:
4008        transform = self.func("TRANSFORM", *expression.expressions)
4009        row_format_before = self.sql(expression, "row_format_before")
4010        row_format_before = f" {row_format_before}" if row_format_before else ""
4011        record_writer = self.sql(expression, "record_writer")
4012        record_writer = f" RECORDWRITER {record_writer}" if record_writer else ""
4013        using = f" USING {self.sql(expression, 'command_script')}"
4014        schema = self.sql(expression, "schema")
4015        schema = f" AS {schema}" if schema else ""
4016        row_format_after = self.sql(expression, "row_format_after")
4017        row_format_after = f" {row_format_after}" if row_format_after else ""
4018        record_reader = self.sql(expression, "record_reader")
4019        record_reader = f" RECORDREADER {record_reader}" if record_reader else ""
4020        return f"{transform}{row_format_before}{record_writer}{using}{schema}{row_format_after}{record_reader}"
4021
4022    def indexconstraintoption_sql(self, expression: exp.IndexConstraintOption) -> str:
4023        key_block_size = self.sql(expression, "key_block_size")
4024        if key_block_size:
4025            return f"KEY_BLOCK_SIZE = {key_block_size}"
4026
4027        using = self.sql(expression, "using")
4028        if using:
4029            return f"USING {using}"
4030
4031        parser = self.sql(expression, "parser")
4032        if parser:
4033            return f"WITH PARSER {parser}"
4034
4035        comment = self.sql(expression, "comment")
4036        if comment:
4037            return f"COMMENT {comment}"
4038
4039        visible = expression.args.get("visible")
4040        if visible is not None:
4041            return "VISIBLE" if visible else "INVISIBLE"
4042
4043        engine_attr = self.sql(expression, "engine_attr")
4044        if engine_attr:
4045            return f"ENGINE_ATTRIBUTE = {engine_attr}"
4046
4047        secondary_engine_attr = self.sql(expression, "secondary_engine_attr")
4048        if secondary_engine_attr:
4049            return f"SECONDARY_ENGINE_ATTRIBUTE = {secondary_engine_attr}"
4050
4051        self.unsupported("Unsupported index constraint option.")
4052        return ""
4053
4054    def checkcolumnconstraint_sql(self, expression: exp.CheckColumnConstraint) -> str:
4055        enforced = " ENFORCED" if expression.args.get("enforced") else ""
4056        return f"CHECK ({self.sql(expression, 'this')}){enforced}"
4057
4058    def indexcolumnconstraint_sql(self, expression: exp.IndexColumnConstraint) -> str:
4059        kind = self.sql(expression, "kind")
4060        kind = f"{kind} INDEX" if kind else "INDEX"
4061        this = self.sql(expression, "this")
4062        this = f" {this}" if this else ""
4063        index_type = self.sql(expression, "index_type")
4064        index_type = f" USING {index_type}" if index_type else ""
4065        expressions = self.expressions(expression, flat=True)
4066        expressions = f" ({expressions})" if expressions else ""
4067        options = self.expressions(expression, key="options", sep=" ")
4068        options = f" {options}" if options else ""
4069        return f"{kind}{this}{index_type}{expressions}{options}"
4070
4071    def nvl2_sql(self, expression: exp.Nvl2) -> str:
4072        if self.NVL2_SUPPORTED:
4073            return self.function_fallback_sql(expression)
4074
4075        case = exp.Case().when(
4076            expression.this.is_(exp.null()).not_(copy=False),
4077            expression.args["true"],
4078            copy=False,
4079        )
4080        else_cond = expression.args.get("false")
4081        if else_cond:
4082            case.else_(else_cond, copy=False)
4083
4084        return self.sql(case)
4085
4086    def comprehension_sql(self, expression: exp.Comprehension) -> str:
4087        this = self.sql(expression, "this")
4088        expr = self.sql(expression, "expression")
4089        iterator = self.sql(expression, "iterator")
4090        condition = self.sql(expression, "condition")
4091        condition = f" IF {condition}" if condition else ""
4092        return f"{this} FOR {expr} IN {iterator}{condition}"
4093
4094    def columnprefix_sql(self, expression: exp.ColumnPrefix) -> str:
4095        return f"{self.sql(expression, 'this')}({self.sql(expression, 'expression')})"
4096
4097    def opclass_sql(self, expression: exp.Opclass) -> str:
4098        return f"{self.sql(expression, 'this')} {self.sql(expression, 'expression')}"
4099
4100    def predict_sql(self, expression: exp.Predict) -> str:
4101        model = self.sql(expression, "this")
4102        model = f"MODEL {model}"
4103        table = self.sql(expression, "expression")
4104        table = f"TABLE {table}" if not isinstance(expression.expression, exp.Subquery) else table
4105        parameters = self.sql(expression, "params_struct")
4106        return self.func("PREDICT", model, table, parameters or None)
4107
4108    def forin_sql(self, expression: exp.ForIn) -> str:
4109        this = self.sql(expression, "this")
4110        expression_sql = self.sql(expression, "expression")
4111        return f"FOR {this} DO {expression_sql}"
4112
4113    def refresh_sql(self, expression: exp.Refresh) -> str:
4114        this = self.sql(expression, "this")
4115        table = "" if isinstance(expression.this, exp.Literal) else "TABLE "
4116        return f"REFRESH {table}{this}"
4117
4118    def toarray_sql(self, expression: exp.ToArray) -> str:
4119        arg = expression.this
4120        if not arg.type:
4121            from sqlglot.optimizer.annotate_types import annotate_types
4122
4123            arg = annotate_types(arg, dialect=self.dialect)
4124
4125        if arg.is_type(exp.DataType.Type.ARRAY):
4126            return self.sql(arg)
4127
4128        cond_for_null = arg.is_(exp.null())
4129        return self.sql(exp.func("IF", cond_for_null, exp.null(), exp.array(arg, copy=False)))
4130
4131    def tsordstotime_sql(self, expression: exp.TsOrDsToTime) -> str:
4132        this = expression.this
4133        time_format = self.format_time(expression)
4134
4135        if time_format:
4136            return self.sql(
4137                exp.cast(
4138                    exp.StrToTime(this=this, format=expression.args["format"]),
4139                    exp.DataType.Type.TIME,
4140                )
4141            )
4142
4143        if isinstance(this, exp.TsOrDsToTime) or this.is_type(exp.DataType.Type.TIME):
4144            return self.sql(this)
4145
4146        return self.sql(exp.cast(this, exp.DataType.Type.TIME))
4147
4148    def tsordstotimestamp_sql(self, expression: exp.TsOrDsToTimestamp) -> str:
4149        this = expression.this
4150        if isinstance(this, exp.TsOrDsToTimestamp) or this.is_type(exp.DataType.Type.TIMESTAMP):
4151            return self.sql(this)
4152
4153        return self.sql(exp.cast(this, exp.DataType.Type.TIMESTAMP, dialect=self.dialect))
4154
4155    def tsordstodatetime_sql(self, expression: exp.TsOrDsToDatetime) -> str:
4156        this = expression.this
4157        if isinstance(this, exp.TsOrDsToDatetime) or this.is_type(exp.DataType.Type.DATETIME):
4158            return self.sql(this)
4159
4160        return self.sql(exp.cast(this, exp.DataType.Type.DATETIME, dialect=self.dialect))
4161
4162    def tsordstodate_sql(self, expression: exp.TsOrDsToDate) -> str:
4163        this = expression.this
4164        time_format = self.format_time(expression)
4165
4166        if time_format and time_format not in (self.dialect.TIME_FORMAT, self.dialect.DATE_FORMAT):
4167            return self.sql(
4168                exp.cast(
4169                    exp.StrToTime(this=this, format=expression.args["format"]),
4170                    exp.DataType.Type.DATE,
4171                )
4172            )
4173
4174        if isinstance(this, exp.TsOrDsToDate) or this.is_type(exp.DataType.Type.DATE):
4175            return self.sql(this)
4176
4177        return self.sql(exp.cast(this, exp.DataType.Type.DATE))
4178
4179    def unixdate_sql(self, expression: exp.UnixDate) -> str:
4180        return self.sql(
4181            exp.func(
4182                "DATEDIFF",
4183                expression.this,
4184                exp.cast(exp.Literal.string("1970-01-01"), exp.DataType.Type.DATE),
4185                "day",
4186            )
4187        )
4188
4189    def lastday_sql(self, expression: exp.LastDay) -> str:
4190        if self.LAST_DAY_SUPPORTS_DATE_PART:
4191            return self.function_fallback_sql(expression)
4192
4193        unit = expression.text("unit")
4194        if unit and unit != "MONTH":
4195            self.unsupported("Date parts are not supported in LAST_DAY.")
4196
4197        return self.func("LAST_DAY", expression.this)
4198
4199    def dateadd_sql(self, expression: exp.DateAdd) -> str:
4200        from sqlglot.dialects.dialect import unit_to_str
4201
4202        return self.func(
4203            "DATE_ADD", expression.this, expression.expression, unit_to_str(expression)
4204        )
4205
4206    def arrayany_sql(self, expression: exp.ArrayAny) -> str:
4207        if self.CAN_IMPLEMENT_ARRAY_ANY:
4208            filtered = exp.ArrayFilter(this=expression.this, expression=expression.expression)
4209            filtered_not_empty = exp.ArraySize(this=filtered).neq(0)
4210            original_is_empty = exp.ArraySize(this=expression.this).eq(0)
4211            return self.sql(exp.paren(original_is_empty.or_(filtered_not_empty)))
4212
4213        from sqlglot.dialects import Dialect
4214
4215        # SQLGlot's executor supports ARRAY_ANY, so we don't wanna warn for the SQLGlot dialect
4216        if self.dialect.__class__ != Dialect:
4217            self.unsupported("ARRAY_ANY is unsupported")
4218
4219        return self.function_fallback_sql(expression)
4220
4221    def struct_sql(self, expression: exp.Struct) -> str:
4222        expression.set(
4223            "expressions",
4224            [
4225                exp.alias_(e.expression, e.name if e.this.is_string else e.this)
4226                if isinstance(e, exp.PropertyEQ)
4227                else e
4228                for e in expression.expressions
4229            ],
4230        )
4231
4232        return self.function_fallback_sql(expression)
4233
4234    def partitionrange_sql(self, expression: exp.PartitionRange) -> str:
4235        low = self.sql(expression, "this")
4236        high = self.sql(expression, "expression")
4237
4238        return f"{low} TO {high}"
4239
4240    def truncatetable_sql(self, expression: exp.TruncateTable) -> str:
4241        target = "DATABASE" if expression.args.get("is_database") else "TABLE"
4242        tables = f" {self.expressions(expression)}"
4243
4244        exists = " IF EXISTS" if expression.args.get("exists") else ""
4245
4246        on_cluster = self.sql(expression, "cluster")
4247        on_cluster = f" {on_cluster}" if on_cluster else ""
4248
4249        identity = self.sql(expression, "identity")
4250        identity = f" {identity} IDENTITY" if identity else ""
4251
4252        option = self.sql(expression, "option")
4253        option = f" {option}" if option else ""
4254
4255        partition = self.sql(expression, "partition")
4256        partition = f" {partition}" if partition else ""
4257
4258        return f"TRUNCATE {target}{exists}{tables}{on_cluster}{identity}{option}{partition}"
4259
4260    # This transpiles T-SQL's CONVERT function
4261    # https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver16
4262    def convert_sql(self, expression: exp.Convert) -> str:
4263        to = expression.this
4264        value = expression.expression
4265        style = expression.args.get("style")
4266        safe = expression.args.get("safe")
4267        strict = expression.args.get("strict")
4268
4269        if not to or not value:
4270            return ""
4271
4272        # Retrieve length of datatype and override to default if not specified
4273        if not seq_get(to.expressions, 0) and to.this in self.PARAMETERIZABLE_TEXT_TYPES:
4274            to = exp.DataType.build(to.this, expressions=[exp.Literal.number(30)], nested=False)
4275
4276        transformed: t.Optional[exp.Expression] = None
4277        cast = exp.Cast if strict else exp.TryCast
4278
4279        # Check whether a conversion with format (T-SQL calls this 'style') is applicable
4280        if isinstance(style, exp.Literal) and style.is_int:
4281            from sqlglot.dialects.tsql import TSQL
4282
4283            style_value = style.name
4284            converted_style = TSQL.CONVERT_FORMAT_MAPPING.get(style_value)
4285            if not converted_style:
4286                self.unsupported(f"Unsupported T-SQL 'style' value: {style_value}")
4287
4288            fmt = exp.Literal.string(converted_style)
4289
4290            if to.this == exp.DataType.Type.DATE:
4291                transformed = exp.StrToDate(this=value, format=fmt)
4292            elif to.this in (exp.DataType.Type.DATETIME, exp.DataType.Type.DATETIME2):
4293                transformed = exp.StrToTime(this=value, format=fmt)
4294            elif to.this in self.PARAMETERIZABLE_TEXT_TYPES:
4295                transformed = cast(this=exp.TimeToStr(this=value, format=fmt), to=to, safe=safe)
4296            elif to.this == exp.DataType.Type.TEXT:
4297                transformed = exp.TimeToStr(this=value, format=fmt)
4298
4299        if not transformed:
4300            transformed = cast(this=value, to=to, safe=safe)
4301
4302        return self.sql(transformed)
4303
4304    def _jsonpathkey_sql(self, expression: exp.JSONPathKey) -> str:
4305        this = expression.this
4306        if isinstance(this, exp.JSONPathWildcard):
4307            this = self.json_path_part(this)
4308            return f".{this}" if this else ""
4309
4310        if exp.SAFE_IDENTIFIER_RE.match(this):
4311            return f".{this}"
4312
4313        this = self.json_path_part(this)
4314        return (
4315            f"[{this}]"
4316            if self._quote_json_path_key_using_brackets and self.JSON_PATH_BRACKETED_KEY_SUPPORTED
4317            else f".{this}"
4318        )
4319
4320    def _jsonpathsubscript_sql(self, expression: exp.JSONPathSubscript) -> str:
4321        this = self.json_path_part(expression.this)
4322        return f"[{this}]" if this else ""
4323
4324    def _simplify_unless_literal(self, expression: E) -> E:
4325        if not isinstance(expression, exp.Literal):
4326            from sqlglot.optimizer.simplify import simplify
4327
4328            expression = simplify(expression, dialect=self.dialect)
4329
4330        return expression
4331
4332    def _embed_ignore_nulls(self, expression: exp.IgnoreNulls | exp.RespectNulls, text: str) -> str:
4333        this = expression.this
4334        if isinstance(this, self.RESPECT_IGNORE_NULLS_UNSUPPORTED_EXPRESSIONS):
4335            self.unsupported(
4336                f"RESPECT/IGNORE NULLS is not supported for {type(this).key} in {self.dialect.__class__.__name__}"
4337            )
4338            return self.sql(this)
4339
4340        if self.IGNORE_NULLS_IN_FUNC and not expression.meta.get("inline"):
4341            # The first modifier here will be the one closest to the AggFunc's arg
4342            mods = sorted(
4343                expression.find_all(exp.HavingMax, exp.Order, exp.Limit),
4344                key=lambda x: 0
4345                if isinstance(x, exp.HavingMax)
4346                else (1 if isinstance(x, exp.Order) else 2),
4347            )
4348
4349            if mods:
4350                mod = mods[0]
4351                this = expression.__class__(this=mod.this.copy())
4352                this.meta["inline"] = True
4353                mod.this.replace(this)
4354                return self.sql(expression.this)
4355
4356            agg_func = expression.find(exp.AggFunc)
4357
4358            if agg_func:
4359                agg_func_sql = self.sql(agg_func, comment=False)[:-1] + f" {text})"
4360                return self.maybe_comment(agg_func_sql, comments=agg_func.comments)
4361
4362        return f"{self.sql(expression, 'this')} {text}"
4363
4364    def _replace_line_breaks(self, string: str) -> str:
4365        """We don't want to extra indent line breaks so we temporarily replace them with sentinels."""
4366        if self.pretty:
4367            return string.replace("\n", self.SENTINEL_LINE_BREAK)
4368        return string
4369
4370    def copyparameter_sql(self, expression: exp.CopyParameter) -> str:
4371        option = self.sql(expression, "this")
4372
4373        if expression.expressions:
4374            upper = option.upper()
4375
4376            # Snowflake FILE_FORMAT options are separated by whitespace
4377            sep = " " if upper == "FILE_FORMAT" else ", "
4378
4379            # Databricks copy/format options do not set their list of values with EQ
4380            op = " " if upper in ("COPY_OPTIONS", "FORMAT_OPTIONS") else " = "
4381            values = self.expressions(expression, flat=True, sep=sep)
4382            return f"{option}{op}({values})"
4383
4384        value = self.sql(expression, "expression")
4385
4386        if not value:
4387            return option
4388
4389        op = " = " if self.COPY_PARAMS_EQ_REQUIRED else " "
4390
4391        return f"{option}{op}{value}"
4392
4393    def credentials_sql(self, expression: exp.Credentials) -> str:
4394        cred_expr = expression.args.get("credentials")
4395        if isinstance(cred_expr, exp.Literal):
4396            # Redshift case: CREDENTIALS <string>
4397            credentials = self.sql(expression, "credentials")
4398            credentials = f"CREDENTIALS {credentials}" if credentials else ""
4399        else:
4400            # Snowflake case: CREDENTIALS = (...)
4401            credentials = self.expressions(expression, key="credentials", flat=True, sep=" ")
4402            credentials = f"CREDENTIALS = ({credentials})" if cred_expr is not None else ""
4403
4404        storage = self.sql(expression, "storage")
4405        storage = f"STORAGE_INTEGRATION = {storage}" if storage else ""
4406
4407        encryption = self.expressions(expression, key="encryption", flat=True, sep=" ")
4408        encryption = f" ENCRYPTION = ({encryption})" if encryption else ""
4409
4410        iam_role = self.sql(expression, "iam_role")
4411        iam_role = f"IAM_ROLE {iam_role}" if iam_role else ""
4412
4413        region = self.sql(expression, "region")
4414        region = f" REGION {region}" if region else ""
4415
4416        return f"{credentials}{storage}{encryption}{iam_role}{region}"
4417
4418    def copy_sql(self, expression: exp.Copy) -> str:
4419        this = self.sql(expression, "this")
4420        this = f" INTO {this}" if self.COPY_HAS_INTO_KEYWORD else f" {this}"
4421
4422        credentials = self.sql(expression, "credentials")
4423        credentials = self.seg(credentials) if credentials else ""
4424        kind = self.seg("FROM" if expression.args.get("kind") else "TO")
4425        files = self.expressions(expression, key="files", flat=True)
4426
4427        sep = ", " if self.dialect.COPY_PARAMS_ARE_CSV else " "
4428        params = self.expressions(
4429            expression,
4430            key="params",
4431            sep=sep,
4432            new_line=True,
4433            skip_last=True,
4434            skip_first=True,
4435            indent=self.COPY_PARAMS_ARE_WRAPPED,
4436        )
4437
4438        if params:
4439            if self.COPY_PARAMS_ARE_WRAPPED:
4440                params = f" WITH ({params})"
4441            elif not self.pretty:
4442                params = f" {params}"
4443
4444        return f"COPY{this}{kind} {files}{credentials}{params}"
4445
4446    def semicolon_sql(self, expression: exp.Semicolon) -> str:
4447        return ""
4448
4449    def datadeletionproperty_sql(self, expression: exp.DataDeletionProperty) -> str:
4450        on_sql = "ON" if expression.args.get("on") else "OFF"
4451        filter_col: t.Optional[str] = self.sql(expression, "filter_column")
4452        filter_col = f"FILTER_COLUMN={filter_col}" if filter_col else None
4453        retention_period: t.Optional[str] = self.sql(expression, "retention_period")
4454        retention_period = f"RETENTION_PERIOD={retention_period}" if retention_period else None
4455
4456        if filter_col or retention_period:
4457            on_sql = self.func("ON", filter_col, retention_period)
4458
4459        return f"DATA_DELETION={on_sql}"
4460
4461    def maskingpolicycolumnconstraint_sql(
4462        self, expression: exp.MaskingPolicyColumnConstraint
4463    ) -> str:
4464        this = self.sql(expression, "this")
4465        expressions = self.expressions(expression, flat=True)
4466        expressions = f" USING ({expressions})" if expressions else ""
4467        return f"MASKING POLICY {this}{expressions}"
4468
4469    def gapfill_sql(self, expression: exp.GapFill) -> str:
4470        this = self.sql(expression, "this")
4471        this = f"TABLE {this}"
4472        return self.func("GAP_FILL", this, *[v for k, v in expression.args.items() if k != "this"])
4473
4474    def scope_resolution(self, rhs: str, scope_name: str) -> str:
4475        return self.func("SCOPE_RESOLUTION", scope_name or None, rhs)
4476
4477    def scoperesolution_sql(self, expression: exp.ScopeResolution) -> str:
4478        this = self.sql(expression, "this")
4479        expr = expression.expression
4480
4481        if isinstance(expr, exp.Func):
4482            # T-SQL's CLR functions are case sensitive
4483            expr = f"{self.sql(expr, 'this')}({self.format_args(*expr.expressions)})"
4484        else:
4485            expr = self.sql(expression, "expression")
4486
4487        return self.scope_resolution(expr, this)
4488
4489    def parsejson_sql(self, expression: exp.ParseJSON) -> str:
4490        if self.PARSE_JSON_NAME is None:
4491            return self.sql(expression.this)
4492
4493        return self.func(self.PARSE_JSON_NAME, expression.this, expression.expression)
4494
4495    def rand_sql(self, expression: exp.Rand) -> str:
4496        lower = self.sql(expression, "lower")
4497        upper = self.sql(expression, "upper")
4498
4499        if lower and upper:
4500            return f"({upper} - {lower}) * {self.func('RAND', expression.this)} + {lower}"
4501        return self.func("RAND", expression.this)
4502
4503    def changes_sql(self, expression: exp.Changes) -> str:
4504        information = self.sql(expression, "information")
4505        information = f"INFORMATION => {information}"
4506        at_before = self.sql(expression, "at_before")
4507        at_before = f"{self.seg('')}{at_before}" if at_before else ""
4508        end = self.sql(expression, "end")
4509        end = f"{self.seg('')}{end}" if end else ""
4510
4511        return f"CHANGES ({information}){at_before}{end}"
4512
4513    def pad_sql(self, expression: exp.Pad) -> str:
4514        prefix = "L" if expression.args.get("is_left") else "R"
4515
4516        fill_pattern = self.sql(expression, "fill_pattern") or None
4517        if not fill_pattern and self.PAD_FILL_PATTERN_IS_REQUIRED:
4518            fill_pattern = "' '"
4519
4520        return self.func(f"{prefix}PAD", expression.this, expression.expression, fill_pattern)
4521
4522    def summarize_sql(self, expression: exp.Summarize) -> str:
4523        table = " TABLE" if expression.args.get("table") else ""
4524        return f"SUMMARIZE{table} {self.sql(expression.this)}"
4525
4526    def explodinggenerateseries_sql(self, expression: exp.ExplodingGenerateSeries) -> str:
4527        generate_series = exp.GenerateSeries(**expression.args)
4528
4529        parent = expression.parent
4530        if isinstance(parent, (exp.Alias, exp.TableAlias)):
4531            parent = parent.parent
4532
4533        if self.SUPPORTS_EXPLODING_PROJECTIONS and not isinstance(parent, (exp.Table, exp.Unnest)):
4534            return self.sql(exp.Unnest(expressions=[generate_series]))
4535
4536        if isinstance(parent, exp.Select):
4537            self.unsupported("GenerateSeries projection unnesting is not supported.")
4538
4539        return self.sql(generate_series)
4540
4541    def arrayconcat_sql(self, expression: exp.ArrayConcat, name: str = "ARRAY_CONCAT") -> str:
4542        exprs = expression.expressions
4543        if not self.ARRAY_CONCAT_IS_VAR_LEN:
4544            rhs = reduce(lambda x, y: exp.ArrayConcat(this=x, expressions=[y]), exprs)
4545        else:
4546            rhs = self.expressions(expression)
4547
4548        return self.func(name, expression.this, rhs or None)
4549
4550    def converttimezone_sql(self, expression: exp.ConvertTimezone) -> str:
4551        if self.SUPPORTS_CONVERT_TIMEZONE:
4552            return self.function_fallback_sql(expression)
4553
4554        source_tz = expression.args.get("source_tz")
4555        target_tz = expression.args.get("target_tz")
4556        timestamp = expression.args.get("timestamp")
4557
4558        if source_tz and timestamp:
4559            timestamp = exp.AtTimeZone(
4560                this=exp.cast(timestamp, exp.DataType.Type.TIMESTAMPNTZ), zone=source_tz
4561            )
4562
4563        expr = exp.AtTimeZone(this=timestamp, zone=target_tz)
4564
4565        return self.sql(expr)
4566
4567    def json_sql(self, expression: exp.JSON) -> str:
4568        this = self.sql(expression, "this")
4569        this = f" {this}" if this else ""
4570
4571        _with = expression.args.get("with")
4572
4573        if _with is None:
4574            with_sql = ""
4575        elif not _with:
4576            with_sql = " WITHOUT"
4577        else:
4578            with_sql = " WITH"
4579
4580        unique_sql = " UNIQUE KEYS" if expression.args.get("unique") else ""
4581
4582        return f"JSON{this}{with_sql}{unique_sql}"
4583
4584    def jsonvalue_sql(self, expression: exp.JSONValue) -> str:
4585        def _generate_on_options(arg: t.Any) -> str:
4586            return arg if isinstance(arg, str) else f"DEFAULT {self.sql(arg)}"
4587
4588        path = self.sql(expression, "path")
4589        returning = self.sql(expression, "returning")
4590        returning = f" RETURNING {returning}" if returning else ""
4591
4592        on_condition = self.sql(expression, "on_condition")
4593        on_condition = f" {on_condition}" if on_condition else ""
4594
4595        return self.func("JSON_VALUE", expression.this, f"{path}{returning}{on_condition}")
4596
4597    def conditionalinsert_sql(self, expression: exp.ConditionalInsert) -> str:
4598        else_ = "ELSE " if expression.args.get("else_") else ""
4599        condition = self.sql(expression, "expression")
4600        condition = f"WHEN {condition} THEN " if condition else else_
4601        insert = self.sql(expression, "this")[len("INSERT") :].strip()
4602        return f"{condition}{insert}"
4603
4604    def multitableinserts_sql(self, expression: exp.MultitableInserts) -> str:
4605        kind = self.sql(expression, "kind")
4606        expressions = self.seg(self.expressions(expression, sep=" "))
4607        res = f"INSERT {kind}{expressions}{self.seg(self.sql(expression, 'source'))}"
4608        return res
4609
4610    def oncondition_sql(self, expression: exp.OnCondition) -> str:
4611        # Static options like "NULL ON ERROR" are stored as strings, in contrast to "DEFAULT <expr> ON ERROR"
4612        empty = expression.args.get("empty")
4613        empty = (
4614            f"DEFAULT {empty} ON EMPTY"
4615            if isinstance(empty, exp.Expression)
4616            else self.sql(expression, "empty")
4617        )
4618
4619        error = expression.args.get("error")
4620        error = (
4621            f"DEFAULT {error} ON ERROR"
4622            if isinstance(error, exp.Expression)
4623            else self.sql(expression, "error")
4624        )
4625
4626        if error and empty:
4627            error = (
4628                f"{empty} {error}"
4629                if self.dialect.ON_CONDITION_EMPTY_BEFORE_ERROR
4630                else f"{error} {empty}"
4631            )
4632            empty = ""
4633
4634        null = self.sql(expression, "null")
4635
4636        return f"{empty}{error}{null}"
4637
4638    def jsonextractquote_sql(self, expression: exp.JSONExtractQuote) -> str:
4639        scalar = " ON SCALAR STRING" if expression.args.get("scalar") else ""
4640        return f"{self.sql(expression, 'option')} QUOTES{scalar}"
4641
4642    def jsonexists_sql(self, expression: exp.JSONExists) -> str:
4643        this = self.sql(expression, "this")
4644        path = self.sql(expression, "path")
4645
4646        passing = self.expressions(expression, "passing")
4647        passing = f" PASSING {passing}" if passing else ""
4648
4649        on_condition = self.sql(expression, "on_condition")
4650        on_condition = f" {on_condition}" if on_condition else ""
4651
4652        path = f"{path}{passing}{on_condition}"
4653
4654        return self.func("JSON_EXISTS", this, path)
4655
4656    def arrayagg_sql(self, expression: exp.ArrayAgg) -> str:
4657        array_agg = self.function_fallback_sql(expression)
4658
4659        # Add a NULL FILTER on the column to mimic the results going from a dialect that excludes nulls
4660        # on ARRAY_AGG (e.g Spark) to one that doesn't (e.g. DuckDB)
4661        if self.dialect.ARRAY_AGG_INCLUDES_NULLS and expression.args.get("nulls_excluded"):
4662            parent = expression.parent
4663            if isinstance(parent, exp.Filter):
4664                parent_cond = parent.expression.this
4665                parent_cond.replace(parent_cond.and_(expression.this.is_(exp.null()).not_()))
4666            else:
4667                this = expression.this
4668                # Do not add the filter if the input is not a column (e.g. literal, struct etc)
4669                if this.find(exp.Column):
4670                    # DISTINCT is already present in the agg function, do not propagate it to FILTER as well
4671                    this_sql = (
4672                        self.expressions(this)
4673                        if isinstance(this, exp.Distinct)
4674                        else self.sql(expression, "this")
4675                    )
4676
4677                    array_agg = f"{array_agg} FILTER(WHERE {this_sql} IS NOT NULL)"
4678
4679        return array_agg
4680
4681    def apply_sql(self, expression: exp.Apply) -> str:
4682        this = self.sql(expression, "this")
4683        expr = self.sql(expression, "expression")
4684
4685        return f"{this} APPLY({expr})"
4686
4687    def grant_sql(self, expression: exp.Grant) -> str:
4688        privileges_sql = self.expressions(expression, key="privileges", flat=True)
4689
4690        kind = self.sql(expression, "kind")
4691        kind = f" {kind}" if kind else ""
4692
4693        securable = self.sql(expression, "securable")
4694        securable = f" {securable}" if securable else ""
4695
4696        principals = self.expressions(expression, key="principals", flat=True)
4697
4698        grant_option = " WITH GRANT OPTION" if expression.args.get("grant_option") else ""
4699
4700        return f"GRANT {privileges_sql} ON{kind}{securable} TO {principals}{grant_option}"
4701
4702    def grantprivilege_sql(self, expression: exp.GrantPrivilege):
4703        this = self.sql(expression, "this")
4704        columns = self.expressions(expression, flat=True)
4705        columns = f"({columns})" if columns else ""
4706
4707        return f"{this}{columns}"
4708
4709    def grantprincipal_sql(self, expression: exp.GrantPrincipal):
4710        this = self.sql(expression, "this")
4711
4712        kind = self.sql(expression, "kind")
4713        kind = f"{kind} " if kind else ""
4714
4715        return f"{kind}{this}"
4716
4717    def columns_sql(self, expression: exp.Columns):
4718        func = self.function_fallback_sql(expression)
4719        if expression.args.get("unpack"):
4720            func = f"*{func}"
4721
4722        return func
4723
4724    def overlay_sql(self, expression: exp.Overlay):
4725        this = self.sql(expression, "this")
4726        expr = self.sql(expression, "expression")
4727        from_sql = self.sql(expression, "from")
4728        for_sql = self.sql(expression, "for")
4729        for_sql = f" FOR {for_sql}" if for_sql else ""
4730
4731        return f"OVERLAY({this} PLACING {expr} FROM {from_sql}{for_sql})"
4732
4733    @unsupported_args("format")
4734    def todouble_sql(self, expression: exp.ToDouble) -> str:
4735        return self.sql(exp.cast(expression.this, exp.DataType.Type.DOUBLE))
4736
4737    def string_sql(self, expression: exp.String) -> str:
4738        this = expression.this
4739        zone = expression.args.get("zone")
4740
4741        if zone:
4742            # This is a BigQuery specific argument for STRING(<timestamp_expr>, <time_zone>)
4743            # BigQuery stores timestamps internally as UTC, so ConvertTimezone is used with UTC
4744            # set for source_tz to transpile the time conversion before the STRING cast
4745            this = exp.ConvertTimezone(
4746                source_tz=exp.Literal.string("UTC"), target_tz=zone, timestamp=this
4747            )
4748
4749        return self.sql(exp.cast(this, exp.DataType.Type.VARCHAR))
4750
4751    def median_sql(self, expression: exp.Median):
4752        if not self.SUPPORTS_MEDIAN:
4753            return self.sql(
4754                exp.PercentileCont(this=expression.this, expression=exp.Literal.number(0.5))
4755            )
4756
4757        return self.function_fallback_sql(expression)
4758
4759    def overflowtruncatebehavior_sql(self, expression: exp.OverflowTruncateBehavior) -> str:
4760        filler = self.sql(expression, "this")
4761        filler = f" {filler}" if filler else ""
4762        with_count = "WITH COUNT" if expression.args.get("with_count") else "WITHOUT COUNT"
4763        return f"TRUNCATE{filler} {with_count}"
4764
4765    def unixseconds_sql(self, expression: exp.UnixSeconds) -> str:
4766        if self.SUPPORTS_UNIX_SECONDS:
4767            return self.function_fallback_sql(expression)
4768
4769        start_ts = exp.cast(
4770            exp.Literal.string("1970-01-01 00:00:00+00"), to=exp.DataType.Type.TIMESTAMPTZ
4771        )
4772
4773        return self.sql(
4774            exp.TimestampDiff(this=expression.this, expression=start_ts, unit=exp.var("SECONDS"))
4775        )
4776
4777    def arraysize_sql(self, expression: exp.ArraySize) -> str:
4778        dim = expression.expression
4779
4780        # For dialects that don't support the dimension arg, we can safely transpile it's default value (1st dimension)
4781        if dim and self.ARRAY_SIZE_DIM_REQUIRED is None:
4782            if not (dim.is_int and dim.name == "1"):
4783                self.unsupported("Cannot transpile dimension argument for ARRAY_LENGTH")
4784            dim = None
4785
4786        # If dimension is required but not specified, default initialize it
4787        if self.ARRAY_SIZE_DIM_REQUIRED and not dim:
4788            dim = exp.Literal.number(1)
4789
4790        return self.func(self.ARRAY_SIZE_NAME, expression.this, dim)
4791
4792    def attach_sql(self, expression: exp.Attach) -> str:
4793        this = self.sql(expression, "this")
4794        exists_sql = " IF NOT EXISTS" if expression.args.get("exists") else ""
4795        expressions = self.expressions(expression)
4796        expressions = f" ({expressions})" if expressions else ""
4797
4798        return f"ATTACH{exists_sql} {this}{expressions}"
4799
4800    def detach_sql(self, expression: exp.Detach) -> str:
4801        this = self.sql(expression, "this")
4802        # the DATABASE keyword is required if IF EXISTS is set
4803        # without it, DuckDB throws an error: Parser Error: syntax error at or near "exists" (Line Number: 1)
4804        # ref: https://duckdb.org/docs/stable/sql/statements/attach.html#detach-syntax
4805        exists_sql = " DATABASE IF EXISTS" if expression.args.get("exists") else ""
4806
4807        return f"DETACH{exists_sql} {this}"
4808
4809    def attachoption_sql(self, expression: exp.AttachOption) -> str:
4810        this = self.sql(expression, "this")
4811        value = self.sql(expression, "expression")
4812        value = f" {value}" if value else ""
4813        return f"{this}{value}"
4814
4815    def featuresattime_sql(self, expression: exp.FeaturesAtTime) -> str:
4816        this_sql = self.sql(expression, "this")
4817        if isinstance(expression.this, exp.Table):
4818            this_sql = f"TABLE {this_sql}"
4819
4820        return self.func(
4821            "FEATURES_AT_TIME",
4822            this_sql,
4823            expression.args.get("time"),
4824            expression.args.get("num_rows"),
4825            expression.args.get("ignore_feature_nulls"),
4826        )
4827
4828    def watermarkcolumnconstraint_sql(self, expression: exp.WatermarkColumnConstraint) -> str:
4829        return (
4830            f"WATERMARK FOR {self.sql(expression, 'this')} AS {self.sql(expression, 'expression')}"
4831        )
4832
4833    def encodeproperty_sql(self, expression: exp.EncodeProperty) -> str:
4834        encode = "KEY ENCODE" if expression.args.get("key") else "ENCODE"
4835        encode = f"{encode} {self.sql(expression, 'this')}"
4836
4837        properties = expression.args.get("properties")
4838        if properties:
4839            encode = f"{encode} {self.properties(properties)}"
4840
4841        return encode
4842
4843    def includeproperty_sql(self, expression: exp.IncludeProperty) -> str:
4844        this = self.sql(expression, "this")
4845        include = f"INCLUDE {this}"
4846
4847        column_def = self.sql(expression, "column_def")
4848        if column_def:
4849            include = f"{include} {column_def}"
4850
4851        alias = self.sql(expression, "alias")
4852        if alias:
4853            include = f"{include} AS {alias}"
4854
4855        return include
4856
4857    def xmlelement_sql(self, expression: exp.XMLElement) -> str:
4858        name = f"NAME {self.sql(expression, 'this')}"
4859        return self.func("XMLELEMENT", name, *expression.expressions)
4860
4861    def xmlkeyvalueoption_sql(self, expression: exp.XMLKeyValueOption) -> str:
4862        this = self.sql(expression, "this")
4863        expr = self.sql(expression, "expression")
4864        expr = f"({expr})" if expr else ""
4865        return f"{this}{expr}"
4866
4867    def partitionbyrangeproperty_sql(self, expression: exp.PartitionByRangeProperty) -> str:
4868        partitions = self.expressions(expression, "partition_expressions")
4869        create = self.expressions(expression, "create_expressions")
4870        return f"PARTITION BY RANGE {self.wrap(partitions)} {self.wrap(create)}"
4871
4872    def partitionbyrangepropertydynamic_sql(
4873        self, expression: exp.PartitionByRangePropertyDynamic
4874    ) -> str:
4875        start = self.sql(expression, "start")
4876        end = self.sql(expression, "end")
4877
4878        every = expression.args["every"]
4879        if isinstance(every, exp.Interval) and every.this.is_string:
4880            every.this.replace(exp.Literal.number(every.name))
4881
4882        return f"START {self.wrap(start)} END {self.wrap(end)} EVERY {self.wrap(self.sql(every))}"
4883
4884    def unpivotcolumns_sql(self, expression: exp.UnpivotColumns) -> str:
4885        name = self.sql(expression, "this")
4886        values = self.expressions(expression, flat=True)
4887
4888        return f"NAME {name} VALUE {values}"
4889
4890    def analyzesample_sql(self, expression: exp.AnalyzeSample) -> str:
4891        kind = self.sql(expression, "kind")
4892        sample = self.sql(expression, "sample")
4893        return f"SAMPLE {sample} {kind}"
4894
4895    def analyzestatistics_sql(self, expression: exp.AnalyzeStatistics) -> str:
4896        kind = self.sql(expression, "kind")
4897        option = self.sql(expression, "option")
4898        option = f" {option}" if option else ""
4899        this = self.sql(expression, "this")
4900        this = f" {this}" if this else ""
4901        columns = self.expressions(expression)
4902        columns = f" {columns}" if columns else ""
4903        return f"{kind}{option} STATISTICS{this}{columns}"
4904
4905    def analyzehistogram_sql(self, expression: exp.AnalyzeHistogram) -> str:
4906        this = self.sql(expression, "this")
4907        columns = self.expressions(expression)
4908        inner_expression = self.sql(expression, "expression")
4909        inner_expression = f" {inner_expression}" if inner_expression else ""
4910        update_options = self.sql(expression, "update_options")
4911        update_options = f" {update_options} UPDATE" if update_options else ""
4912        return f"{this} HISTOGRAM ON {columns}{inner_expression}{update_options}"
4913
4914    def analyzedelete_sql(self, expression: exp.AnalyzeDelete) -> str:
4915        kind = self.sql(expression, "kind")
4916        kind = f" {kind}" if kind else ""
4917        return f"DELETE{kind} STATISTICS"
4918
4919    def analyzelistchainedrows_sql(self, expression: exp.AnalyzeListChainedRows) -> str:
4920        inner_expression = self.sql(expression, "expression")
4921        return f"LIST CHAINED ROWS{inner_expression}"
4922
4923    def analyzevalidate_sql(self, expression: exp.AnalyzeValidate) -> str:
4924        kind = self.sql(expression, "kind")
4925        this = self.sql(expression, "this")
4926        this = f" {this}" if this else ""
4927        inner_expression = self.sql(expression, "expression")
4928        return f"VALIDATE {kind}{this}{inner_expression}"
4929
4930    def analyze_sql(self, expression: exp.Analyze) -> str:
4931        options = self.expressions(expression, key="options", sep=" ")
4932        options = f" {options}" if options else ""
4933        kind = self.sql(expression, "kind")
4934        kind = f" {kind}" if kind else ""
4935        this = self.sql(expression, "this")
4936        this = f" {this}" if this else ""
4937        mode = self.sql(expression, "mode")
4938        mode = f" {mode}" if mode else ""
4939        properties = self.sql(expression, "properties")
4940        properties = f" {properties}" if properties else ""
4941        partition = self.sql(expression, "partition")
4942        partition = f" {partition}" if partition else ""
4943        inner_expression = self.sql(expression, "expression")
4944        inner_expression = f" {inner_expression}" if inner_expression else ""
4945        return f"ANALYZE{options}{kind}{this}{partition}{mode}{inner_expression}{properties}"
4946
4947    def xmltable_sql(self, expression: exp.XMLTable) -> str:
4948        this = self.sql(expression, "this")
4949        namespaces = self.expressions(expression, key="namespaces")
4950        namespaces = f"XMLNAMESPACES({namespaces}), " if namespaces else ""
4951        passing = self.expressions(expression, key="passing")
4952        passing = f"{self.sep()}PASSING{self.seg(passing)}" if passing else ""
4953        columns = self.expressions(expression, key="columns")
4954        columns = f"{self.sep()}COLUMNS{self.seg(columns)}" if columns else ""
4955        by_ref = f"{self.sep()}RETURNING SEQUENCE BY REF" if expression.args.get("by_ref") else ""
4956        return f"XMLTABLE({self.sep('')}{self.indent(namespaces + this + passing + by_ref + columns)}{self.seg(')', sep='')}"
4957
4958    def xmlnamespace_sql(self, expression: exp.XMLNamespace) -> str:
4959        this = self.sql(expression, "this")
4960        return this if isinstance(expression.this, exp.Alias) else f"DEFAULT {this}"
4961
4962    def export_sql(self, expression: exp.Export) -> str:
4963        this = self.sql(expression, "this")
4964        connection = self.sql(expression, "connection")
4965        connection = f"WITH CONNECTION {connection} " if connection else ""
4966        options = self.sql(expression, "options")
4967        return f"EXPORT DATA {connection}{options} AS {this}"
4968
4969    def declare_sql(self, expression: exp.Declare) -> str:
4970        return f"DECLARE {self.expressions(expression, flat=True)}"
4971
4972    def declareitem_sql(self, expression: exp.DeclareItem) -> str:
4973        variable = self.sql(expression, "this")
4974        default = self.sql(expression, "default")
4975        default = f" = {default}" if default else ""
4976
4977        kind = self.sql(expression, "kind")
4978        if isinstance(expression.args.get("kind"), exp.Schema):
4979            kind = f"TABLE {kind}"
4980
4981        return f"{variable} AS {kind}{default}"
4982
4983    def recursivewithsearch_sql(self, expression: exp.RecursiveWithSearch) -> str:
4984        kind = self.sql(expression, "kind")
4985        this = self.sql(expression, "this")
4986        set = self.sql(expression, "expression")
4987        using = self.sql(expression, "using")
4988        using = f" USING {using}" if using else ""
4989
4990        kind_sql = kind if kind == "CYCLE" else f"SEARCH {kind} FIRST BY"
4991
4992        return f"{kind_sql} {this} SET {set}{using}"
4993
4994    def parameterizedagg_sql(self, expression: exp.ParameterizedAgg) -> str:
4995        params = self.expressions(expression, key="params", flat=True)
4996        return self.func(expression.name, *expression.expressions) + f"({params})"
4997
4998    def anonymousaggfunc_sql(self, expression: exp.AnonymousAggFunc) -> str:
4999        return self.func(expression.name, *expression.expressions)
5000
5001    def combinedaggfunc_sql(self, expression: exp.CombinedAggFunc) -> str:
5002        return self.anonymousaggfunc_sql(expression)
5003
5004    def combinedparameterizedagg_sql(self, expression: exp.CombinedParameterizedAgg) -> str:
5005        return self.parameterizedagg_sql(expression)
5006
5007    def show_sql(self, expression: exp.Show) -> str:
5008        self.unsupported("Unsupported SHOW statement")
5009        return ""
5010
5011    def get_put_sql(self, expression: exp.Put | exp.Get) -> str:
5012        # Snowflake GET/PUT statements:
5013        #   PUT <file> <internalStage> <properties>
5014        #   GET <internalStage> <file> <properties>
5015        props = expression.args.get("properties")
5016        props_sql = self.properties(props, prefix=" ", sep=" ", wrapped=False) if props else ""
5017        this = self.sql(expression, "this")
5018        target = self.sql(expression, "target")
5019
5020        if isinstance(expression, exp.Put):
5021            return f"PUT {this} {target}{props_sql}"
5022        else:
5023            return f"GET {target} {this}{props_sql}"
5024
5025    def translatecharacters_sql(self, expression: exp.TranslateCharacters):
5026        this = self.sql(expression, "this")
5027        expr = self.sql(expression, "expression")
5028        with_error = " WITH ERROR" if expression.args.get("with_error") else ""
5029        return f"TRANSLATE({this} USING {expr}{with_error})"
5030
5031    def decodecase_sql(self, expression: exp.DecodeCase) -> str:
5032        if self.SUPPORTS_DECODE_CASE:
5033            return self.func("DECODE", *expression.expressions)
5034
5035        expression, *expressions = expression.expressions
5036
5037        ifs = []
5038        for search, result in zip(expressions[::2], expressions[1::2]):
5039            if isinstance(search, exp.Literal):
5040                ifs.append(exp.If(this=expression.eq(search), true=result))
5041            elif isinstance(search, exp.Null):
5042                ifs.append(exp.If(this=expression.is_(exp.Null()), true=result))
5043            else:
5044                if isinstance(search, exp.Binary):
5045                    search = exp.paren(search)
5046
5047                cond = exp.or_(
5048                    expression.eq(search),
5049                    exp.and_(expression.is_(exp.Null()), search.is_(exp.Null()), copy=False),
5050                    copy=False,
5051                )
5052                ifs.append(exp.If(this=cond, true=result))
5053
5054        case = exp.Case(ifs=ifs, default=expressions[-1] if len(expressions) % 2 == 1 else None)
5055        return self.sql(case)
5056
5057    def semanticview_sql(self, expression: exp.SemanticView) -> str:
5058        this = self.sql(expression, "this")
5059        this = self.seg(this, sep="")
5060        dimensions = self.expressions(
5061            expression, "dimensions", dynamic=True, skip_first=True, skip_last=True
5062        )
5063        dimensions = self.seg(f"DIMENSIONS {dimensions}") if dimensions else ""
5064        metrics = self.expressions(
5065            expression, "metrics", dynamic=True, skip_first=True, skip_last=True
5066        )
5067        metrics = self.seg(f"METRICS {metrics}") if metrics else ""
5068        where = self.sql(expression, "where")
5069        where = self.seg(f"WHERE {where}") if where else ""
5070        return f"SEMANTIC_VIEW({self.indent(this + metrics + dimensions + where)}{self.seg(')', sep='')}"